Microsoft SQL Server Interview Questions and Answers
You want to be sure that your Scalable Shared
Database is as available as possible. Which of the
following is not needed for this?
Use Database Mirroring to fail over between the old
reporting database and the new one.
The update process for a Scalable Shared database with
minimal downtime involves putting out a new copy of the
database, detaching the old database from each server,
and then attaching the new database to each server.
What is the cost threshhold for parallelism in SQL
This is the number of seconds that a serialplan cannot
exceed if it is to be used. A parallel plan is used if
the estimate exceeds this value.
This is the threshold at which SQL Server determines
whether a serial or parallel plan is to be used. When
SQL Server calculates that a serial plan exceeds the
threshold, it will elect to use a parallel plan instead.
You have a Scalable Shared Database setup for reporting
purposes on SQL2. You want to be able to keep a point in
time view of the reporting database each month. What can
Make a new copy of the production database each month
and then copy that to the SAN. Attach it as a new
Scalable Shared Database each month to the reporting
A Scalable Shared Database does not support database
snapshots, so you would have to manually create a new
database each month with the data view you need and add
this as a new Scalable Shared Database to the SAN and
each reporting server.
You have an old database that needs to run in
compatibility mode 65 on your SQL Server 2005 server.
Which framework would you use to manage this database
SMO does not support compatibility modes 60 or 65, so
you would need to use DMO instead.
You have two Service Broker instances running. One is on
SQL1 with the default collation and the other is on SQL2
setup for French collation. Which collation is used for
Service Broker messages sent between the instances?
Service Broker does not consider the collation, using
byte-by-byte matching for names.
Neither collation is used. Service Broker operates in a
collation independent method that removes collation
information from the messages.
What does the max full-text crawl range option do?
Determines the number of partitions used in an index
This option helps optimize the full-text indexing
process by specifying the number of partitions the SQL
Server uses during index crawls.
Which of the following is not an allocation unit in SQL
The three types of allocation units are: IN_ROW_DATA,
LOB_DATA, and ROW_OVERFLOW_DATA. Each heap or index has
IN_ROW_DATA which holds part of the data. LOB_DATA is
used for large object data types and ROW_OVERFLOW_DATA
is used for varible length data that causes a row to
exceed the 8060 byte limit.
Which of the following is the best use for a Scalable
Shared Database in SQL Server 2005?
A reporting database server
A scalable shared database is a feature that allows you
to setup read-only database on a separate server for
reporting purposes. This database provides an identicle
view of your data from another server.
You are loading 100 rows of data into a narrow table
that is heavily used by your production inventory
queries. It was recommended that you drop the indexes on
the table before the load and then rebuild them after
the load is complete. Is this something you would do?
This does not make sense.
For such a small number of rows, it is unlikely that
dropping the indexes will improve the performance of
your load. If this were 100,000 rows, then it might make
How can SQL Server Agent Mail send messages in SQL
SQL Mail through Extended MAPI or Database mail.
SQL Server Agent Mail can be configured to use Database
Mail or Extended MAPI.
What is the scale of measurement for the cost threshold
for parallelism setting in SQL Server 2005?
This value measures the number of seconds for a plan
where the optimizer chooses between serial and parallel
Which of the following statements best describes the
filter capabilities of Report Builder?
Users can do equals, greater than, less than, etc, plus
they can do logical AND, OR, NOT operations. Users can
also group filters to allow more advanced filters.
While it looks a little different than you may be used
to, the filter builder is reasonably rich, allowing most
standard evaluation types and rich boolean comparisons.
The only weak spot in the set is no support for LIKE,
you have to make do with CONTAINS.
True or false, Report Builder supports user defined run
Absolutely true. Users can define any portion of a
filter to be a run time prompt, letting other users
easily change the filter as needed. Not only is it easy
to set up, Report Builder automatically populates a list
of all possible choices based on the column being
Using Report Builder that is bundled with Reporting
Services 2005, which of the following would work as a
way to add a derived field to a report?
Add a field to the model in Report Builder using the
built in formula/function support
While Report Builder cannot be used to build or maintain
models, it does allow you to add a virtual field that
exists only within that report - to the end user it
looks like the model is being modified.
If you absolutely need a report to look the same
regardless of what OS or viewing software is being used,
which of the following file formats would be the best
TIFF is the correct answer. By rendering as an image
there are no concerns about different fonts, problems
with page breaks, etc. PDF's are almost as good and more
commonly used of course, with the advantage that
document maps are translated to bookmarks that are
usable - something not possible with a raw image.
True or false, Report Builder offers direct export to a
Microsoft Access database as one of its supported export
False. Supported formats include Excel, CSV, XML, TIFF,
MHTML, and PDF. XML or CSV could be easily imported into
Access, but there is way to add the data directly from
When discussing image support in Report Builder, which
of the following is the most accurate statement?
Users can add one or more images, but they will appear
in the header or footer of the report, they cannot be
added to the detail row. Images that are stored as row
data in the database can be rendered at the detail
Multiple images can be added, but they appear in the
header or footer depending on where placed on the
report. The only way to get an image at the detail level
(row based) is to have it be part of the database and
included in the model.
Page Numbers : 1