Microsoft SQL Server Interview Questions and Answers
What does this return?
SELECT USER_NAME() -- Returns Andy
EXECUTE AS TechPreparation
EXECUTE AS Steve
The execution context switches can be nested, so
changing to TechPreparation, then Steve, then issuing a Revert
will return you to the context of
TechPreparation. 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?
Images stored originally in formats other than jpg are
rendered in png format.
What does the tablediff utility do?
This utility will report the differences in data between
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.)
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
The timestamp datatype does not store time values.
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
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?
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?
You can monitor the Assembly Load event in a trace.
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
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?
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?
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
None of the above.
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).
What's the difference between a server login and a
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.
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?
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.
What is a bookmark lookup?
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
Yesterday's QOD How are comments denoted in an XML
With the <!-- and --> markers.
Comments in an XML document are denoted just as they are
in HTML with the markers.
How secure are encrypted stored procedures, triggers and
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.
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
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:
The Service Broker operates on messages in which
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
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?
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
What does this method in SQL Server 2005's SMO equate to
DBCC CHECKDB WITH NO_INFOMSGS
This SMO method executes a DBCC CHECKDB with the
NO_INFOMSGS option set.
Page Numbers : 1