(Continued from previous part...)

What does the function ORIGINAL_LOGIN return in SQL Server 2005?

Answer: The login used to connect to SQL Server.
Explanation: This function returns the name of the login originally used to connect to SQL Server for a session, regardless of any context switching that has occurred.

You are performing a number of SELECT queries on one of your SQL Server 2005 Service Broker queues and you get a call that applications using the queues are running slowly. What can you do?

Answer: Use the NOLOCK hint when querying the queue.
Explanation: Multiple queue readers can access a queue, but SELECT queries can cause blocking, so you should use the NOLOCK hint when querying the queue.

What's an allocation unit?

Answer: A collection of pages in a table based on page type.
Explanation: An allocation unit is a collection of pages within a heap or B-tree. It is based on on the page type (LOB data in row, LOB data out of row, overflow data). It can be of any size and crosses extents.

Your SQL Server 2005 Express Edition locks up frequently and cannot be accessed. How can you enable the dedicated administrator connection?

Answer: You must use a trace flag to enable Express to listen with a DAC.
Explanation: The dedicated administrator connection is not normally available in SQL Server 2005 Express Edition, but with trace flag 7806 it can be enabled.

What does VARP() do in SQL Server 2005?

Answer: This function returns the statistical variance of the values in the column.
Explanation: This function returns a statistical variance for the population of values on which it is run.

When declaring an XML document, which type allows text nodes at the top level?

Answer: CONTENT
Explanation: The CONTENT XML variable declaration allows txt nodes at the top level. This is the default behavior.

Can a contained in SQL Server 2005 Integration Services enlist in a transaction started by a sibling container?

Answer: No
Explanation: Containers can only enlist in transactions started by a parent container, not a sibling container.

What does a return value of -1 mean from the function XACT_STATE() in SQL Server 2005?

Answer: This indicates that the current transaction cannot be committed.
Explanation: This indicates the the current transaction has entered an uncommittable state.

In SQL Server 2005, what is the maximum size of an index key?

Answer: 900 bytes
Explanation: The maximum size for SQL Server 2005 is the same as SQL Server 2000; it is 900 bytes. This does not include nonkey columns that are included.

What does this return? select 10 / 3

Answer: 3
Explanation: This returns 3. Since both of the values are integers, the result will also be an integer. If either of these were a decimal, as in select 10 / 3.3 or select 10.0 / 3, then the result would be 3.3333.

When you declare an XML variable in SQL Server 2005, what are the two types of typed XML data. What are they?

Answer: CONTENT and DOCUMENT
Explanation: The two types you can declare are CONTENT and DOCUMENT.

(Continued on next part...)