(Continued from previous part...)

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

Answer
Yes

Explanation
Yes you can.

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

Answer
The service is installed once for each instances on the server.

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

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
No

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

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

Answer
8

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

Is it possible to build an index on a view

Answer
Yes

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

What does REVERT do in SQL Server 2005?

Answer
Restores your previous execution context.

Explanation
If you have changed your execution context with EXECUTE AS, the REVERT statement will restore the last context priot to the EXECUTE AS.

What does SEND do in SQL Server 2005?

Answer
Sends a service broker message using a conversation.

Explanation
SEND is used to send a message on an existing conversation in the Service Broker architecture.

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

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

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()
go
revert
select user_name()
go

Answer
You will receive as results:

bob
An error

Explanation
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 recieve the following error:

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? Submitted by Apress

Answer
RANK alongside the OVER function where the OVER function uses the players number of strokes taken

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

(Continued on next part...)