SQL Server 2000 Questions and Answers

Answer: D

Correct answer: @

A single at sign marks a local variable or a stored procedure parameter, and two mark a system global. The convention is part of the Transact-SQL syntax rather than a naming habit, so the sign is obligatory - a declaration without it will not compile.

Answer: A

Correct answer: True

A new database gets one data file, with the .mdf extension, and one log file with .ldf. Further data files can be added and placed on other disks for capacity or performance, but the arrangement created by default is the simplest one that works, and it keeps the data and the log separate from the start.

Answer: A

Correct answer: True

Transact-SQL is SQL Server’s procedural extension to SQL, adding variables, parameters, conditionals, loops and error handling. Stored procedures, triggers and user-defined functions are all written in it, and it is the counterpart of Oracle’s PL/SQL.

Answer: B

Correct answer: False

It is the INSTEAD OF trigger that may be placed on a view, and that is its principal purpose - making updatable a view that the DBMS could not otherwise update. An AFTER trigger fires once a statement has completed against a table and cannot be defined on a view.

Answer: A

Correct answer: True

Read committed is the default: it prevents dirty reads by taking shared locks while reading, but releases them as soon as the read is done, so nonrepeatable reads and phantoms remain possible. It is the default because it prevents the anomaly that matters most at the least cost in concurrency.

Answer: B

Correct answer: False

A clustered index is generally the faster for retrieval, because the data rows are at its leaf level and no further lookup is needed once the index has been traversed. A nonclustered index yields a pointer, and the row must then be fetched from wherever it lies, which is a second access for every row returned.

Answer: A

Correct answer: True

A unique index on the primary key is created automatically because the uniqueness must be enforced somehow, and foreign keys are indexed as well. The foreign key index is what makes joins to the parent efficient and what allows referential integrity to be checked without scanning the child table.

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.

Answer: B

Correct answer: False

It stands for database owner. The dbo is the default schema and the account that owns the database, so an object referred to as dbo.Customer is the Customer table in the dbo schema. Reading it as database object would suggest a type of thing rather than an owner, which is the wrong idea entirely.

Answer: A

Correct answer: True

A clustered index holds the data rows at its leaf level in index order, so there can be only one; a nonclustered index holds keys and pointers, and there may be many. Every other kind of index in SQL Server is a variation on these two, and the choice of which column carries the clustered index is the more consequential decision.