SQL Server 2000 Questions and Answers

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

Correct answer: SQL Server only security

SQL Server offers Windows-only authentication and mixed mode, in which either a Windows account or a SQL Server login may be used. There is no mode that disables Windows authentication, since a Windows administrator can always reach the instance. Windows-only is the more secure of the two available, as the credentials are managed by the domain.

Answer: D

Correct answer: INSTEAD OF and AFTER only

SQL Server provides AFTER triggers, firing once the statement has completed, and INSTEAD OF triggers, replacing it. There is no BEFORE trigger, which is a genuine difference from Oracle and one of the things to check when porting trigger code between the two products.

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

Correct answer: True

Under Windows-only authentication the credentials are managed by the domain, with its password policies, account lockout and central administration, and no password is stored or transmitted by SQL Server itself. Mixed mode adds SQL Server logins, which are convenient for applications and non-Windows clients but are one more set of credentials to manage and protect.

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.