DBA > Interview Resource

Microsoft SQL Server FAQs

Part:   1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40  41 

(Continued from previous part...)

In the Windows world, if you hit the "Enter" key on your keyboard between two single quotes and inserted this value, what data would be stored in the column?

Answer: ASCII 13 and ASCII 10
Explanation: In the Windows world, the Enter key creates two characeters: ASCII 13 and ASCII 10. The 13 is a carriage return and the 10 is a line feed. This is different than Unix based systems.


You wish to back up the symmetric key used to protect data on the Reporting Services 2005 instance installed on Sales01. From your workstation, what command do you use?

Answer: You cannot do this from your workstation.
Explanation: While you can extract and back up the key to a file with the rskeymgmt utility, you must do it from the server itself. The typical command is:
rskeymgmt -e -f a:\backupkey\keys -p <password>


You have a notification set up in SQL Server 2005 to let you know when a job fails. However you would like more details about the step that failed. Which table would you query in your report to get logging information about a job step?

Answer: msdb.dbo.sysjobstepslogs
Explanation: The MSDB.dbo.sysjobstepslogs table contains information about each step that is configured to log its data. You can query this table for information about the job.


You want to speed up your SQL Server 2005 Service Broker queue by placing an index on it. What is the best way to do this?

Answer: You cannot create an index on a Service Broker queue.
Explanation: You cannot create indexes on SQL Server 2005 Service Broker queues.


Which is these is not necessary for a well-formed XML document?

Answer: Contains no text at the top level element.
Explanation: By definition, a well formed XML document has 3 parts:
* contains one or more elements
* It has a root element
* It is well formed and all entities referenced in the document are well-formed


You want to create a covering index for this query on your SQL Server 2005 database:
select product, cost, productdescription
from products
where productid = @productid

The columns are as follows:
* productid - int
* product - nvarchar(80)
* cost - money
* productdescription - nvarchar(500)
What is the best solution?

Answer: CREATE INDEX IX_Cover ON dbo.Products (productid, product, cost) INCLUDE (productdescription);
Explanation: An index key can only have 900 bytes in total for all columns, so creating an index on all the columns would not work. Instead you can "include" additional columns in SQL Server 2005 that are not counted towards the 900 byte limit.
In this case, moving the productdescription to the INCLUDE clause would result in a valid index that covers this query.


On which port does the dedicated administrator connection listen in SQL Server 2005?

Answer: a Dynamic port assigned on server start.
Explanation: The port is assigned dynamically. You can get the port from the error log after SQL Server has started.


What is the default type of XML data if you do not specify an arguement?

Answer: CONTENT
Explanation: The default type of ata is the CONTENT data type, which can have multiple zero or more elements at the top level and contain text nodes at the top level.


You created a SQL Server 2005 Service Broker queue, but neglected to add an activation procedure. Fortunately you had not set the status to ON. You use the ALTER QUEUE statement to specify a stored procedure as follows: ALTER QUEUE PublicationQueue WITH ACTIVATION ( PROCEDURE_NAME = new_stored_proc, EXECUTE AS SELF) ; However the procedure does not seem to run when you send a message to the queue. What is wrong?

Answer: The queue activation status needs to be set to on.
Explanation: When you alter a queue and change the activation stored procedure, this does not affect the activation status of the queue. You could need to alter the queue again with the "ACTIVATION (STATUS = ON)" parameter.


When you use the sp_xml_preparedocument stored procedure in SQL Server 2000, what is returned?

Answer: An integer acting as a handle to the document.
Explanation: The handle, or pointer to the XML document is returned. This is an integer value that you can use to refer to the document in other stored procedures.

(Continued on next part...)

Part:   1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40  41