(Continued from previous part...)

What's the difference between a server login and a database user?

Answer
A server login connects an account to the server. A database user the link from the server login to a defined database .
Explanation
You need to create a server login using CREATE LOGIN before you can then link it to a database using a database user with CREATE USER. The server login logs into the server and grants access to any server wide permissions. The server login is then mapped to a database user for access to database objects.

For regulatory reasons, you must maintain an exact text copy of your XML documents in your SQL Server 2005 database. What datatype of storage should you choose?

Answer
varchar(max)

Explanation
The XML data type changes the representation of your XML document into an internal, optimized version that has all the content, but may not maintain the original text. The varchar(max) or nvarchar(max) represenations should be used in this case.

What is a bookmark lookup?

Answer
An operation where the row in the heap or clustered index is found from the bookmark in the index.

Explanation
A bookmark ID is stored in an index and points back to the actual row in the heap or clustered index of the table. The lookup operation occurs when an index satisfies a search and the actual row is retrieved from the table.

Yesterday's QOD How are comments denoted in an XML document?

Answer
With the <!-- and --> markers.

Explanation
Comments in an XML document are denoted just as they are in HTML with the markers.

How secure are encrypted stored procedures, triggers and views?

Answerbr> They are not really secure, that are only obfusticated. There are tools that you can use to get at the code.br> br> Explanationbr> The code itself is just obfusticated. However, you cannot edit the code, the stored procedure or view would have to be dropped and re-created. By combing this fact with a DDL trigger that executes on object creation you can tell when it has been modified.

You have created a new assembly and want to test it on SQL Server 2005. You install the developer edition with defaults on your workstation and create the assembly as a sysadmin. You then write a function to use this assembly, but it does not work. What could be the problem?

Answer
The CLR environment needs to be enabled.

Explanation
By default on SQL Server 2005, the CLR runtime environment, which is what executes the .NET assemblies, is not enabled. You need to execute the following code to enable it:

The Service Broker operates on messages in which fashion?

Answer
Like a queue, first in, first out.

Explanation
Messages transferred in Service Broker work in a queue fashion with the first message being sent being the first processed.

You have a user that agrees to take over some of the database administration for your SQL Server 2005. They will be in charge of granting access to one particular database used by the time card application for server logins. What security role should you assign them?

Answer
db_accessadmin

Explanation
This user will not add logins, but rather grant database level access for existing logins, therefore the minimum role they need is the db_accessadmin fixed database role. This allows them the ability to add access for Windows logins, Windows group logins, or SQL Server logins.

What does this method in SQL Server 2005's SMO equate to in T-SQL?
db.CheckTables(RepairType.None)

Answer
DBCC CHECKDB WITH NO_INFOMSGS

Explanation
This SMO method executes a DBCC CHECKDB with the NO_INFOMSGS option set.

(Continued on next part...)