Set - 7

Question 1 :

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 .
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.

Question 2 :

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 :

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.

Question 3 :

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.
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.

Question 4 :

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

Answer :

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

Question 5 :

How secure are encrypted stored procedures, triggers and views?

Answer :

They are not really secure, that are only obfusticated. There are tools that you can use to get at the code.
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.