SQL Server 2000 Questions and Answers

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

Correct answer: Both of the two methods above will work.

Tables may be created and altered by writing CREATE TABLE and ALTER TABLE statements, or through the graphical designer in Enterprise Manager, which generates the same statements underneath. The graphical route is quicker for a single change; the SQL is what can be scripted, version controlled and applied identically to another server.

Answer: B

Correct answer: False

That describes SCROLL_LOCK, the pessimistic setting. Optimistic concurrency takes no lock while reading and merely checks at update time whether the row has changed since it was read, failing the update if it has. The assumption is that conflict is rare, which is what makes it the faster option when it holds.

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

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

Correct answer: False

That describes a full backup. A differential copies only what has changed since the last full backup, which is why it is quicker to take and why restoring needs the full backup and the latest differential together. Each differential grows until the next full backup resets the baseline.

Answer: B

Correct answer: be kept within the database and invoked by application programs.

A stored procedure is compiled and held in the database itself and called by name from an application, which is what gives it its advantages: the code lives in one place, the execution plan is reused, only the call and the parameters cross the network, and permissions can be granted on the procedure rather than on the tables.

Answer: B

Correct answer: False

That describes optimistic concurrency. Pessimistic locking is the opposite assumption: a lock is taken when the row is read and held while the application works on it, so no one else can change it in the meantime. The two names describe the assumption made about how likely conflict is.