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

What does this return?
SELECT USER_NAME() -- Returns Andy
EXECUTE AS Brian
GO
EXECUTE AS Steve
GO
REVERT
GO
SELECT USER_NAME()

Answer
Brian

Explanation
The execution context switches can be nested, so changing to Brian, then Steve, then issuing a Revert will return you to the context of Brian. This works in the same manner as a stack.


In building a PDF report in Reporting Services 2005, images that are originally stored in jpg format are rendered in jpg format. What format are images rendered in if they were stored originally in another format?

Answer
png

Explanation
Images stored originally in formats other than jpg are rendered in png format.


What does the tablediff utility do?

Answer
This utility will report the differences in data between two tables.

Explanation
This utility will report the data differences between two tables. They must be alike in structure and give back the T-SQL to bring the data in the tables to be the same for most datatypes.


Which severity levels of errors can be corrected by a user (as opposed to those that an administrator or Microsoft must work to fix.)

Answer
11-16

Explanation
Severity levels 11-16 are correctable by users. Below 11, these are informational warnings and not raised as system errors. Errors above 16 must have an administrator or Microsoft correct.


What is the granularity in seconds of the timestamp datatype?

Answer
The timestamp datatype does not store time values.

Explanation
The answer is the timestamp datatype does not store time values. The better name for this datatype is rowversion, and it is actually a database-wide unique varbinary value. When you have a rowversion column in a table, the value of the rowversion column changes for each modification to each row. The value in the rowversion column is guaranteed to be unique across all tables in the datatype.


You are setting up the backup scheme for your SQL Server 2005 server and want to setup nightly full backups and hourly log backups in the Maintenance Plans subsystem. How many plans must you setup?

Answer
2

Explanation
Since you have two separate schedules for the maintenance operations, you will need to have two separate plans to handle this need. Each plan can only be executed on one schedule, so one is needed for a single daily execution, the nightly full backups, and another for the hourly log backups.


You have installed one new assembly on your SQL Server 2005 server and are wondering if it is being used on the production server. How can you easily monitor to see if this assembly is being used?

Answer
You can monitor the Assembly Load event in a trace.

Explanation
While you could scan trace results for the names of functions and procedures using the assembly, it is possible that you may not know all the places the assembly is being called from. There is a CLR Load event class that will capture an event when a CLR class is executed.


You wish to ensure you can recover your SQL Server 2005 database to a point in time if necessary. Which recovery models can you use?

Answer
Full
Explanation
Only the Full recovery model supports point in time restoration of your database.


Does a root element in an XML document necessarily contain all the content for a well-formed document?

Answer
Yes

Explanation
By definition, the root element is required in a well-formed XML document and it contains all other content nested inside it.


Which of the following datatypes can be represented in a SQL_VARIANT datatype?

Answer
None of the above.

Explanation
The SQL Variant type can store all datatypes except varchar(max), varbinary(max), xml, text, ntext, rowversion/timestamp (thought the data of a rowversion can be stored in a binary(8), which can be stored in a sql_variant) and sql_variant (it may seem strange that you can’t store a variant in a variant, but all this is saying is that the sql_variant data type doesn’t actually exist as such—SQL Server chooses the best type of storage to store the value you give to it).

(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