SQL Server 2000 Questions and Answers

Answer: C

Correct answer: OPTIMISTIC

Optimistic concurrency takes no lock while the row is read and merely checks at update time whether the row has changed since. If it has, the update fails and the application must decide what to do. It assumes conflict is rare, which is what makes it faster than holding a lock through the whole read.

Answer: C

Correct answer: TRANSACT SQL.

Transact-SQL adds procedural constructs - variables, parameters, conditionals, loops, error handling - to standard SQL, so that logic can be written and executed inside the database. It is what stored procedures, triggers and functions are written in, and it is the SQL Server counterpart of Oracle’s PL/SQL.

Answer: B

Correct answer: Clustered

A clustered index stores the data rows themselves at its leaf level and in index order, so the index is the table. There can be only one per table for that reason, and choosing which column carries it is a significant design decision, since range queries on that column then need no further lookup.

Answer: C

Correct answer: Differential backup

A differential backup copies everything that has changed since the last full backup, so restoring needs the full backup and the most recent differential only. It is quicker than a full backup and simpler to restore than a long chain of transaction log backups, at the price of growing steadily until the next full backup resets it.

Answer: A

Correct answer: Nonclustered

A nonclustered index holds only the key values and pointers to the rows, which live elsewhere in whatever order the clustered index or the heap imposes. A table may have many of them, and each costs storage and maintenance on every write, so they are added where a query needs one rather than as a matter of course.

Answer: B

Correct answer: Full recovery

Under the full model every change is logged and the log is retained until backed up, so the database can be restored to any point in time and no committed transaction need be lost. It is the model for anything where the day’s work matters, and it obliges the administrator to back the log up regularly or it will grow without bound.

Answer: C

Correct answer: both primary and foreign keys.

SQL Server creates a unique index on the primary key automatically, since it must enforce uniqueness, and it indexes foreign keys as well. Both are worth having: the primary key index enforces entity integrity, and the foreign key index makes the joins and the referential integrity checks efficient.

Answer: A

Correct answer: Lock level

Concurrency behaviour in SQL Server is determined by the transaction isolation level, the cursor concurrency setting and any locking hints supplied in the statement. Lock level, meaning granularity, is chosen by the DBMS itself according to the work being done, and is not something the developer sets as one of these three controls.

Answer: C

Correct answer: AFTER trigger.

An AFTER trigger fires once the statement has completed and the constraints have been checked, so it sees the result and can act on it or roll it back. It is the ordinary kind, used for auditing, for maintaining derived data and for enforcing rules too complex for a constraint.

Answer: A

Correct answer: REPEATABLE READ

Repeatable read holds shared locks on every row it reads until the transaction ends, so no other transaction can change a row that has been read and a second read gives the same values. It does not lock ranges, so a row inserted into the range can still appear - the phantom that serializable prevents.