(Continued from previous part...)

Which of the following columns can be indexed with SQL Server 2005 Full-Text Search?

Answer
char, varchar, nvarchar, and varbinary, text, ntext, and image
Explanation
All character columns, char, varchar and nvarchar columns including max, text and ntext, and image columns are valid for full-text searching.

When starting SQL Server 2005 from the command line, what does the -h switch do?

Answer
This switch reserves memory space for Hot-Add memory metadata, available with AWE enabled.
Explanation
This switch is used with 32-bit SQL Server and AWE to reserve memory to reserve memory space for Hot-Add memory metadata.

Janice has two tables, Employees and Orders. She has been asked to provide a report of the number of orders processed by each employee in the month of June. If an employee didn’t process any orders, the report should reflect the employee’s name and a zero for the number of orders. Which of the queries is the best one for Janice to use to return the information she has been requested to provide? Submitted by Brian Kelley

Answer
SELECT
E.LastName + ', ' + E.FirstName AS [Employee Name]
, ISNULL(O.[# of Orders], 0) [# of Orders]
FROM dbo.Employees E
LEFT JOIN (SELECT
EmployeeID
, COUNT(*) [# of Orders]
FROM dbo.Orders
WHERE OrderDate >= '20060601'
AND OrderDate < '20060701'

GROUP BY EmployeeID) O
ON E.EmployeeID = O.EmployeeID
ORDER BY [Employee Name]

Explanation
While it would seem BETWEEN would save Janice a bit of typing, there is a problem with it. BETWEEN corresponds, based on these queries to the same as:

WHERE OrderDate >= ‘20060601’ AND OrderDat e<= ‘20060701’
It’s the latter one that causes the query to be incorrect as it would potentially include orders placed on July 1st at midnight (20060701 corresponds to July 1, 2006 at 00:00:00). While this would be unlikely in a small retail environment, it is entirely possible in larger operations, especially those which are international in scope. Therefore, the use of BETWEEN in this case is not appropriate.
With respect to the GROUP BY, it must appear in the subquery on the Orders table, as given in the answer. When it occurs after the LEFT JOIN, the rows where there is an employee but no order for the month will be lost. Since Janice must report on employees who had no sales, this is unacceptable.

In SQL Server 2005, you want to monitor your transactional replication and determine how long it takes for a transaction to move from the publisher to a subscriber. What would you use to help you do this?

Answer
Tracer Tokens.

Explanation
You can use tracer tokens in SQL Server 2005 replication to measure latencies. These are small amounts of data written into your transaction logs.

How many credentials can one login be mapped to?

Answer
1

Explanation
A credential can be mapped to many logins, but a login can only be mapped to one credential.

(Continued on next part...)