(Continued from previous part...)

In Service Broker for SQL Server 2005, you encounter the following setting in one of your queues.
RETENTION = ON
What does this mean?

Answer: This means that all messages in a conversation are retained in the queue until the conversation ends.
Explanation: This setting means that all messages on a conversation are retained in the queue until the conversations have ended. This allows for auditing or the ability to generate offsetting transactions. The default is off.

Can a deterministic computed column be full-text indexed in SQL Server 2005? Submitted by Matija Lah

Answer: Yes

Explanation: Yes. While full-text indexes are not supported on deterministic computed columns in SQL 2000, this limitation no longer exists in SQL 2005. You cannot index non-deterministic and imprecise computed columns.

What does the PAD_INDEX parameter do when you create an index in SQL Server 2005?

Answer: Determines if space is left at the intermediate pages of indexes.

Explanation: If PAD_INDEX is set to ON, then the fillfactor is used to determine how much space is left in the intermediate pages of an index. If set to OFF, then the intermediate pages are filled near their limit with space being left for at least one row.

In SQL Server 2005, what does the CREATE SERVICE command do?

Answer: Adds a new Service Broker set of tasks.

Explanation: This command is used to create a new Service Broker service, or set of tasks. This name is used to route messages, deliver messages to a queue, and enforce the contract for a conversation.

What does HAS_DBACCESS() do?

Answer: Returns a 1 if the current user has access to the database passed in. Explanation: This function returns a 1 if the current user has access to the database named passed to the function and 0 if they do not. It is a way to check access before changing to that database.

In SQL Server 2005 T-SQL, what does the CONTINUE statement do?

Answer: Restarts a WHILE loop.

Explanation: This statement restarts a WHILE loop. Any statements inside the loop after the CONTINUE command are ignored and the test condition for the WHILE loop is reexecuted.

You need to remove a large table from your database. This table has over 200 extents of data and you issue a "DROP TABLE MYLARGETABLE". How is this handled in SQL Server 2005?

Answer: A two part scheme of destruction with a virtual sniper marking each allocation unit for deletion inside the transaction. Then in smaller separate transactions of destruction the allocation units are dropped.

Explanation: While a virtual phase locked laser would be nice, this actually occurs in two phases. The logical phase occurs inside the transaction with the drop statement and all of the allocation units are marked for deallocation and locked. After the transaction commits, the allocation units marked are physically dropped in short batches inside background transactions.

If you set RETENTION = ON in your SQL Server 2005 Service Broker queue, what messages are retained?

Answer: Incoming and outgoing messages.

Explanation: Both incoming and outgoing messages are retained in the queue. Outgoing messages are copied to the queue.

(Continued on next part...)