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.

Question 6 :

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

Question 7 :

The Service Broker operates on messages in which fashion?

Answer :

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

Question 8 :

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 :

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.

Question 9 :

What does this method in SQL Server 2005's SMO equate to in T-SQL?

Answer :

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

Question 10 :

In SQL Server 2005, can you include a user-defined role as a member of another user-defined role?

Answer :

Yes you can.

Question 11 :

In SQL Server 2005, what does instance aware mean for a service?

Answer :

The service is installed once for each instances on the server.
An instance aware service is one that is installed for each instance on the server. An instance-unaware service is only installed once , no matter how many instances. Integration Services is instance-unaware whereas SQL Agent is instance aware.

Question 12 :

If you use the EXECUTE AS clause to impersonate a user at a database-scoped level. Can you execute a query through a linked server if the impersonated user has the rights to do so when normally logged in? No trust relationships exist between the databases.

Answer :

If you change your execution context to a user, which is scoped at a database level, then any queries to linked servers or other databases will fail. There is a way around this restriction if trust relationships are setup.

Question 13 :

The nesting level for stored procedures in SQL Server 2005, but how many databases can you access inside one stored procedure?

Answer :

The footnote for nested stored procedures limits the number at 8.

Question 14 :

Is it possible to build an index on a view ?

Answer :

Yes, an index can be put on a view but the first index must be a unique clustered index.

Question 15 :

What does REVERT do in SQL Server 2005?

Answer :

Restores your previous execution context.
If you have changed your execution context with EXECUTE AS, the REVERT statement will restore the last context prior to the EXECUTE AS.

Question 16 :

What does SEND do in SQL Server 2005?

Answer :

Sends a service broker message using a conversation.
SEND is used to send a message on an existing conversation in the Service Broker architecture.

Question 17 :

In SQL Server 2005, most data for your data types is stored in the pages for the table. Binary objects, such as image or text, are stored in LOB or large Object data pages. Where is varchar(max) data stored?

Answer :

In LOB pages
Since the size restrictions for varchar(max) are the same as for text data, this data is stored in LOB data pages.

Question 18 :

What will occur with the following code in SQL Server 2005 if executed by Alice?
execute as user = 'Bob' with no revert
select user_name()
select user_name()

Answer :

You will receive as results:

An error

The NO REVERT option with EXECUTE AS prevents the return of execution context to the previous value. If you run the REVERT statement, you will receive the following error:

Question 19 :

You have to create some T-SQL that produces an order of players in a golf tournament. Players who finish on the same number of strokes have to be given the same finishing place denoting that they will have an equal prize amount. What T-SQL function can achieve this?

Answer :

RANK alongside the OVER function where the OVER function uses the players number of strokes taken
The rank function will return the rank of the row in the result set. Ties will receive the same rank. The OVER clause will separate out the ranking into partitions and use those for calculating the proper placement of the golfers.

Question 20 :

You want to disable the trigger dEmployee on the HumanResources.Employee table in the SQL Server 2005 AdventureWorks sample database. Which of the following statements will do the trick?

Answer :

A and B
Either of the commands in A and B will disable the trigger.

Question 21 :

In SQL Server 2005, if you want the keys securing your data to expire over time, what type of key encryption would you choose?

Answer :

Certificates have an expiration data associated with them. This allows you to issue them to users and force a reissue in order to maintain their access to data.

Question 22 :

Which is larger in size in SQL Server 2005 if you are using symmetric keys?

Answer :

Ciphertext is larger and can be calculated using the following formula:
Size = ( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK + 16)
where D is the data size in bytes, BLOCK is the block size (8 or 16, depending on algorithm) and Size is the new size in bytes of the ciphertext.

Question 23 :

What result would you expect from this statement?

Answer :

Select * from dbo

All records from [dbo].[MyTable]
The answer is all records from [dbo].[MyTable]. Qualified names can include white space before and after the dots. Restrictions apply only to the names themselves that may need quoted identifiers with embedded white space or invalid characters.

Question 24 :

You are using SQL Server 2005 and you wish to find a number of details about stored procedures created. The details include date and time, the server that created the stored procedure, the login name and the contents. How would you do this?

Answer :

Create a DDL trigger on CREATE_PROCEDURE database event
The answer is Create a DDL trigger on CREATE_PROCEDURE database event You can find the answer on page 448 of Beginning SQL Server 2005 for Developers: From Novice to Professional

Question 25 :

A .NET assembly running with UNSAFE permissions can do which of the following?

Answer :

All of the above
CLR code running with UNSAFE permissions can access virtually any resource inside or outside of SQL Server without restriction. This is one reason only a sysadmin can create an assembly as UNSAFE.