Microsoft SQL Server Interview Questions and Answers
You have a SQL Server 2005 sales database
experiencing performance problems because of heavy I/O
activity. You decide to create a snapshot of this
database every hour, stored on a separate physical disk
array and use that for reporting queries. However the
queries still seem slow immediately after snapshot
The snapshot still queries the original database.
The snapshot of the production database will only reduce
I/O for changed pages. Any data that has not been
changed will be read from the source database, which is
in this case is experiencing heavy I/O itself.
In Reporting Services 2005, how is Report Builder
typically deployed to end users?
One click deployment launched from a menu on the Report
Manager home page
One click deployment is fast and easy, users click the
menu on the Report Manager page. It is not a web
application, but rather a .Net Winform application.
Native Web Services require what type of endpoint in SQL
Native XML Web Services in SQL Server 2005 require HTTP
endpoints to communicate with clients.
If you have a replication scenario with one publisher,
one distributor and one subscriber and the transaction
isolation level is not set to serializable, what happens
when a transaction fails on the publisher?
It is still sent to the distributor and executed on the
A transaction that fails in the publication database is
still sent to the distributor and subscriber. YUou can
trap the error, but you would need to trap it on the
subscriber as well.
When creating a full-text index on a BLOB column (image,
binary or varbinary) where a file is stored, what
happens if an incorrect file prefix is stored in the
type column? (For instance: if a word document is stored
in the image or a varbinary(MAX) column and the value of
the type column is '.pdf'.
The row is not full-text indexed and an error message is
written to the SQL Server Error Log
The answer is the row is not full-text indexed and an
error message is written to the SQL Server Error Log. No
columns in the row are full-text indexed and an error
message is written to the SQL Server Error Log.
In SQL Server 2005 Full-Text Search, you set up a
replacement set like this:
<sub>SQL Server 2005 </sub>
<sub>SQL Server 2K5 </sub>
A search for "SS2K5" doesn't return results with fields
that you know contain "SS2K5". What is wrong?
The replacement set does not return results with the
search terms, only the specified replacements.
In the SQL Server 2005 thesaurus XML configuration file,
what is the expansion set?
Expansion sets are synonyms for the search term and
returned as results if they appear along with the search
The expansion set is the group of values that are
synonyms and can be substituted for the search term. For
example, an expansion set can be "SS2K5", "SQL Server
2005", "SQL2K5". In this case, fields with any of these
3 values would be returned as a result for searches on
"SQL Server 2005".
You are initiating a new replication subscription for
one of the remote offices, but don't want to transfer it
across the network. The snapshot files are 725MB, too
large for your CD burner and there is no tape drive at
the remote office. What feature of SQL Server 2005 would
help you get the snapshot transferred?
In SQL Server 2005, you can specify a snapshot to be
compressed and it will be compressed using the CAB
format. If it compresses enough, you should be able to
burn it onto a CD.
The TRUSTWORTHY database property is by default?
The correct answer is off – The TRUSTWORTHY database
property indicates whether the installed instance of SQL
Server trusts the database such that it can reach out
and perform actions outside the database. By default it
is OFF such that the database to reduce certain threats
that can result from attaching a database that contains
potentially harmful code
Where are Notification Services event messages logged in
SQL Server 2005?
In the Windows Application Log
Event messages are logged in the Windows Application
Page Numbers : 1