SQL Server 2000 Questions and Answers

Answer: B

Correct answer: False

Parameters and local variables begin with a single at sign, and system globals with two. The per cent sign is the wildcard in a LIKE pattern and has nothing to do with variables, so a declaration using it will not compile.

Answer: A

Correct answer: INSTEAD OF trigger.

An INSTEAD OF trigger replaces the statement that fired it, so the code in the trigger runs and the original insert, update or delete does not. Its principal use is on views that are not otherwise updatable: the trigger receives the intended change and writes it to the underlying tables itself.

Answer: B

Correct answer: SERIALIZABLE.

Serializable is the strictest of the four, permitting neither dirty reads, nor nonrepeatable reads, nor phantoms, and giving a result equivalent to running the transactions one after another. The cost is the concurrency given up: locks are held on ranges as well as rows, so other transactions are blocked from inserting into them.

Answer: A

Correct answer: True

Serializable permits none of the three anomalies - no dirty reads, no nonrepeatable reads and no phantoms - and gives a result equivalent to running the transactions one at a time. It achieves the last of these by locking key ranges as well as rows, which is what blocks the insertion that would otherwise produce a phantom.

Answer: B

Correct answer: False

SQL Server supports INSTEAD OF and AFTER triggers only; there is no BEFORE trigger. Much of what a BEFORE trigger does elsewhere is achieved with an INSTEAD OF trigger, which replaces the statement entirely and can perform whatever checks are wanted before writing the change itself.

Answer: B

Correct answer: False

There are two ways: writing CREATE TABLE or ALTER TABLE statements, or using the graphical designer in Enterprise Manager. Both end in the same SQL being executed, and the choice is practical - the graphical route for a quick change, the script for anything that must be repeated on another server or kept under version control.

Answer: D

Correct answer: SCROLL_LOCK

SCROLL_LOCK is the pessimistic setting: an update lock is taken as each row is read and held while the cursor is positioned there, so no one else can change the row before the application decides what to do with it. OPTIMISTIC takes no lock until the update is attempted, and READ_ONLY takes none at all.

Answer: A

SQL Server provides three different recovery models that allow you to specify the way SQL Server manages log files and prepares your enterprise for a disaster. Each of these models represents a different approach to balancing the tradeoff between conserving disk space and providing for granular disaster recovery options. The three disaster recovery models offered by SQL Server are:

1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-logged Recovery Model

Answer: D

Correct answer: Simple recovery

Under the simple model the log is truncated at each checkpoint, so no log is retained for recovery purposes and the database can only be restored to the last full or differential backup. Everything since is lost. It suits a development or read-mostly database, and is unsuitable for anything where the day’s transactions matter.

Answer: D

Correct answer: Bulk-logged recovery

Bulk-logged recovery logs everything except the bulk operations - bulk inserts, index creation, text and image writes - which are recorded minimally rather than row by row. It is a compromise: the log stays small during a bulk load, at the price of losing point-in-time recovery within the interval in which one occurred.