SQL Server 2000 Questions and Answers

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: 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: True

Square brackets delimit an identifier, so a reserved word or a name containing spaces or other special characters must be bracketed to be read as a name. Without them the parser takes the word in its reserved sense and reports a syntax error. The double quote serves the same purpose when the quoted identifier setting is on.

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: 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: 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: 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.

Answer: A

Correct answer: True

An index is an auxiliary structure - in SQL Server a balanced tree - that lets rows be found by key value without scanning the table. It costs storage and must be maintained on every insert, update and delete, so it is a trade of write cost and space for read speed, made where a query actually needs it.

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

Stored procedures in SQL Server 2000 are written in Transact-SQL, and .NET languages could not be used for them at all - CLR integration arrived in SQL Server 2005. Even after it did, T-SQL remained the ordinary choice, the CLR being reserved for work that genuinely needs a general purpose language.