(Continued from previous part...)

In SQL Server 2000, how can you send an alert without sending a message to the client?

Answer: xp_logevent

Explanation: The xp_logevent extended stored procedure will log an event in the SQL Server log file and the Windows Event log without sending a message to the client.

Which permission in SQL Server 2005 allows you to switch your security context to that of another user with the EXECUTE AS statement?

Answer: Impersonate

Explanation: The IMPERSONATE permission is required to change context to that of another login.

What does this WHERE clause evaluate to? (there are rows with NULL values)
select top 10 *
from MyTable
where Myid = NULL

Answer: Unknown

Explanation: The expression "MyID = NULL" evaluates to Unkown rather than any true or false condition.

You need to create a route for your SQL Server 2005 Service Broker queue. The messages should go to the SalesOrder queue on the Sales server. When you are creating the route, what value should you use for the BROKER_INSTANCE parameter?

Answer: It is the GUID of the Service Broker instance running in the target database.

Explanation: The BROKER_INSTANCE parameter is looking for the guid of the particular Service Broker instance in the target database. This value can be retrieved by running this query:
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()

Which type of XML variable requires a\one and only one root element?

Answer: DOCUMENT

Explanation: The DOCUMENT XML data requires that there be one and only one root element. The CONTENT type allows multiple elements at the top level.

Which of these is not a parameter you can pass to the LOGINPROPERTY function?

Answer: IsNTAuth

Explanation: IsNTAuth is not a property you can retrive. You can get IsLocked, IsExpired, IsMustChange, BadPasswordCount, BadPasswordTime, HistoryLength, LockoutTime, PasswordLastSetTime, and PasswordHash.

What does the NULLIF function do?

Answer: Returns NULL if the two expressions passed in are equal.

Explanation: This function returns NULL if the two expressions passed into it are equal.

When using the SQLCMD tool, what does the -V parameter do?

Answer: This specifies the lowest severity level that sqlcmd will report errors from.

Explanation: This parameter specifies the lowest severity level that sqlcmd will report when a T-SQL error occurs.

What should you have applied a patch for by now on your Windows 2003 and XP systems?

Answer: Daylight Savings Time

Explanation: If you haven't, daylight savings time comes early, this Sunday in fact. Be sure you patch your systems, set an Outlook reminder, and get up Sunday to check servers.

You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?

Answer: ALTER INDEX with the REORGANIZE option

Explanation: You should use the ALTER INDEX with the REORGANIZE option because the DBCC commands have been deprecated.

(Continued on next part...)