Set - 7

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.