Microsoft SQL Server Interview Questions and Answers
When you install SQL Server 2005 and create a new
database, is a Database Master Key created?
A database master key is not created when a database is
created. It must be created by an administrator.
What does the DEALLOCATE statement do in SQL Server
Remove a reference to a cursor.
This statement is used to remove cursor references. If
this is the last reference being removed, the cursor's
resources are released.
Report Builder has no built in support for source
control. Which of the following choices could be
implemented if you decided that those reports needed to
be under source control?
Have users save the RDL files locally and check into
source control using a source control utility, or write
code to script them out each night and check them into
Users can do faux source control by just saving copies
of their RDL files locally before they make a change.
Beyond that, you either teach them how to use a source
control utility or you write a utility you can run on a
Setting the TRUSTWORTHY database property to ON allows
you to execute the following from within the context of
Objects from unsigned CLR assemblies with
EXTERNAL_ACCESS or UNSAFE permissions
In order to execute objects from CLR assemblies with
EXTERNAL_ACCESS or UNSAFE permissions, the assemby must
either be signed with a key pair or the database hosting
the assembly must have the TRUSTWORTHY database property
set to ON. Executing the other answers is essentially
controlled by permissions. Setting the TRUSTWORTHY
database property to ON also allows you to use the
EXECUTE AS clause to allow impersonation outside the
scope of the database within the instance but this was
not one of the answers.
What tool is available for ad hoc reporting by non IT
end users in Reporting Services 2005?
Report Builder is bundled as part of Reporting Services
2005 and is targeted at end users needing to build ad
hoc reports. BI Design Studio could be used, but
typically requires more time and experience to use than
the average end user will be willing to invest.
You have just upgraded your SQL Server 2000 instance on
Windows 2000 to SQL Server 2005. You are looking to
begin implementing encryption capabilities and need to
choose an algorithm for your data. Which of the
following is not available to you?
While all of these are valid encryption algorithms on
SQL Server 2005, the AES algorithms (128-bit, 192-bit,
and 256-bit) are not supported on Windows XP or Windows
2000. To use these, you would need to upgrde to Windows
Which types of replication work with Oracle publishers?
Snapshot and transactional
Oracle publishing with SQL Server works as both snapshot
and transactional publication issues.
What does @@textsize return?
The current value of the TEXTSIZE option for the SET
This returns the current value of the TEXTSIZE option
that can be changed with the SET command. The default is
In configuring the thesaurus configuration file for SQL
Server 2005 Full-Text Search, you set the
diacritics_sensitive value to 1. What does this mean?
It means the sets in this file are accent sensitive.
The diacritics_sensitive determines if the terms in the
expansion and replacement sets are accent sensitive. A
value of 0 means accent insensitive and a value of 1
means accent sensitive.
Before a user can build reports using Report Builder in
Reporting Services 2005, which one of the following
steps must be done first?
Build and deploy a model
Report Builder requires a model and that model cannot be
defined using Report Builder. The advantage of using
models is that users do not need to understand SQL
statements to build a report (but they will still
benefit from a basic understanding of the relationships
between various bits of data)
What does the CREATE SERVICE statement do in SQL Server
This statement is used to setup new tasks for Service
This statement is ue to create a new Service Broker
service that is a set of tasks that are run on messages.
Page Numbers : 1