What is SQL Server?
SQL Server is a DBMS system provided by Microsoft. SQL Server is
sometimes mistakenly referred to as SQL.
Error severity 13 indicates what?
Transactional deadlock error. This level of error severity indicates a
transaction deadlock error.
In which order do you perform an upgrade to SQL Server 2005 for replicated
databases?
Distributor, Publisher and then Subscriber. You always perform an upgrade in
this order: distributor, publisher, subscriber.
How many Service Packs will be released for SQL Server 2005 in 2007?
Explanation: The answer is up in the air and this is more of a poll than a real
QOD. Based on the ways things are going, the staff here sees just 1, though our
hope would be that 3 or 4 would be released.
You setup a linked server from a SQL Server 2000 server to your new SQL
Server 2005 server (with defaults), however you cannot execute procedures
on the 2005 server. Why not?
You need to enable RPC. By default, RPC is disabled in SQL Server 2005. You
need to set the "remote access option" in your server configuration to 1 to allow the
execution of stored procedures from a remote server.
What is the recommended way to send mail from SQLAgent in SQL Server
2005?
Database Mail. You can use either Database Mail or SQLMail with SQL Agent
in SQL Server 2005. However since SQLMail will be removed, it is recommended that
you use Database Mail.
When you create a new Message Type in the SQL Server 2005 Service
Broker, what does the Authorization parameter signify?
The owner of the message type. This parameter determines the owner of the
message type. This defaults to the current user.
What the heck does ATN2 do?
The angle between the x-axis and a ray. This is a mathematical function that
returns the angle between the positive x-axis and the ray that passes through the
two coordinates passed in. The angle is in radians.
SQL Server Interview Questions
http://marancollects.blogspot.com 2/35
How does a differential backup know which extents have changed so that it
can be very quickly run?
The DCM tracks changes. The differential backup reads the extents from this
structure. A differential backup uses the Differential Change Map to determine which
extents have changed and need to be include in the backup. This greatly speeds the
differential backup process.
What does the Queue Reader Agent do in SQL Server 2005 replication?
This agent reads the subscriber logs and moves changes back to the
publisher. This agent is used when the queued update model is chosen with
transactional replication. It moves changes from the subscribers back to the
publishers.
What are the three possible functions of the plus (+) operator in SQL Server
2005, the base installed T-SQL?
Add, string concatenation, unary plus. The three functions are Add, String
Concatenation, and Unary Plus.
The Sort component in SQL Server 2005 Integration Services is what type of
component?
Blocking Transformation. The Sort component is a blocking transformation
meaning that it needs all rows in order to perform its function.
If you received a "Performance Critical" status in the SQL Server 2005
replication monitor, what does this mean?
The latency between transaction commits at the publisher and subscriber
exceeds the warning level. This status can actually mean two different things. Either
the latency between the commit of a transaction at the publisher and the same
commit at the subscriber is exceeding some level in a transactional level or not
enough rows are being processed in a merge replication scenario.
Which of the following modules within SQL Server 2005 cannot be signed
with a digital signature?
DDL triggers. DDL triggers cannot be signed, but all the other objects can.
What does this return?
declare @i int
select @i = -5
select +@i
-5
SQL Server Interview Questions
http://marancollects.blogspot.com 3/35
This will return -5 as the result. The + operator functions as a unary plus
operator, which means that it performs no operation on the value it preceeds.
You have installed a US English SQL Server 2000 instance with the default
options, collation, and sorting. What does this return?
create table MyTable ( Mychar varchar(20))
go
insert Mytable select 'Apple'
insert Mytable select 'ant'
insert Mytable select 'Ball'
go
select * from MyTable where Mychar like '[^a]%' Ball
This should return "Ball" only since the ^ operator means not matching the
next character. In this case, the first character should not be an "A".
What is the Service Broker Identifier?
A GUID that identifies the database on which Service Broker is running. Each
database has a Service Broker identifier. This is a GUID in the service_broker_GUID
column that identifies the databases on which Service Broker is running. It ensure
that messages are delivered to the right database.
You are looking to import a large amount of data from a remote OLEDB data
source that is not a text file. Which of the following techniques can you use?
Use the select * from OPENROWSET(BULK...) command. SQL Server 2005
includes a new option with the OPENROWSET command for getting large amounts of
data from an OLEDB data source. It is the BULK option and works similar to the
BULK INSERT command.
How are modified extents tracked in SQL Server 2005 (which internal
structures)?
Differential Change Map and Bulk Change Map. There are two internal
structures that track extents modified by bulk copy operations or that have changed
since the last full backup. They are the Differential Changed Map (DCM) and the Bulk
Changed Map (BCM).
What does this return? select (1324 & 1024)
1024. This performs a bitwise AND operation between the two integers and
sets the result to this. Since 1024 is a single set bit in it's value, if the corresponding
bit is set to 1, then in the result the bit is set to 1. In this case, since no other bits
would generate two 1s, the result is equivalevt to the mask, or 1024.
SQL Server Interview Questions
http://marancollects.blogspot.com 4/35
What does the Log Reader agent in SQL Server 2005 replication do?
This agent reads the publisher log for transactions to send to the distributor.
This agent is tasked with reading the transaction log in transactional replication and
moving those transactions that need to be replicated to the distributor.
You are performing an update of your Scalable Shared Database and receive
note that two reports run at the same time received different results. These
reports were both run during your update. What type of update did you
perform?
A rolling update. When performing a rolling update, doing the detach and
attach on each server as opposed to detaching from all then attaching to all, it is
possible that different reporting servers will display different results.
What does a @@fetch_status of -2 mean in SQL Server 2005?
The row being fetched is missing. This means that the row that was being
fetched from the cursor is missing.
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 Server 2005?
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 you do?
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 servers. 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.
SQL Server Interview Questions
http://marancollects.blogspot.com 5/35
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 programmatically?
SQL-DMO. 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 crawl.
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 Server 2005?
TEXT_IN_ROW_DATA. 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 sense.
How can SQL Server Agent Mail send messages in SQL Server 2005?
SQL Server Interview Questions
http://marancollects.blogspot.com 6/35
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?
Seconds. This value measures the number of seconds for a plan where the
optimizer chooses between serial and parallel plans.
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 time parameters?
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 filtered.
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 choice?
TIFF. 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 options?
SQL Server Interview Questions
http://marancollects.blogspot.com 7/35
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 Report Builder
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 level.
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.
Using Report Builder, which of the following is the best statement about the
formatting options for Boolean columns?
Booleans are formatted as True/False and there are no other built in options,
but you could build an expression using IIF that would let you do other formatting
From the format dialog there are no extra formatting options for Booleans, Report
Builder renders them as True/False. Writing an expression that you add to the model
view is the easiest way to work around this limitation.
What is the easiest way to capture the SQL statement for a Report Builder
report you're troubleshooting?
Run Profiler. Profiler will work as long as you have permissions to profile the
server and is the the best solution because it requires no change to the Report
Server itself. There is a way to log all report SQL to a log file, but that option was not
listed here and is better used if you want to do analysis rather than troubleshooting.
Clicking File, Save in Report Builder does which of the following?
Saves the report to the report server. File|Save writes the report to the
Report Server. Users have the option to also save the report to disk by using
File|Save to File. Report Builder users cannot modify the model.
Which of the following choices show the three report formats supported by
Report Builder?
Table, Matrix, Chart. Report Builder can build a report formatted as a table,
chart, or matrix (cross tab), but only ONE can be used in any given report.
Using Report Builder, which of the following statements is correct about
formatting numbers?
SQL Server Interview Questions
http://marancollects.blogspot.com 8/35
Users can pick from a small number of predefined formats and they have the
option to specify a custom format. There are give built in formats; general, currency,
percentage, two place decimal, and exponent. Users can also define a custom format
using a .Net format string.
True or false, Report Builder supports using the LIKE function inside filters?
False. There is no LIKE support, the next best thing is the CONTAINS function
which works as if you specified both a leading and trailing wild card.
Which RAID levels store parity information?
RAID 5. Only RAID 5 (of those listed) contains parity information.
You have a large table that you wish to partition to improve performance.
The table contains many columns of data about customers and you decide
that basic information about each customer will remain in the current table.
Extended information, such as shipping instructions, secretaries' names,
etc. will be moved to a new table along with the PK. What type of
partitioning is this?
Vertical partitioning. If you are moving some columns from one table to a new
table, this is vertical partitioning.
On which platforms can you use Instant File Initialization to improve
database growth performance in SQL Server 2005?
Windows 2003 and XP Pro. Both Windows 2003 Server and later as well as
Windows XP Professional support Instant File Initialization.
You have created a database snapshot on SQL Server 2005 for the sales
database to capture the end-of-month activity. The next day your server
fails and you need to recover to a standby server using the previous night's
backups. How do you recover the snapshot?
There is nothing you can do. The snapshot is lost. Database snapshots cannot
be backed up, so once the server failed, the database snapshot was lost.
Using Reporting Services 2005, it is true or false that subreports execute a
query against the datasource once for every detail row in the report?
True. Subreports can be used for a master-detail relationship, or the
subreport can be a separate item, but in either case RS will query to get the data for
the report once for each detail row. If end users are going to only occasionally look
at the data you're displaying in the subreport or only view it for a few rows, a better
option is to create a link to the other report.
SQL Server Interview Questions
http://marancollects.blogspot.com 9/35
You have noticed in both your SQL Server 2000 and 2005 instances that
when a database grows in SQL Server, there is a delay in the database
response. Why is that?
Once the file is grown, zeros are written to the new space, causing a delay.
When a database file grows, unless instant file initialization is turned on, the server
must allocate disk space and then write 0s into all that space. This zero-ing out of
the file creates the delay.
Which utility is used to administer SQL Server 2005 Notification Services
instances?
nscontrol.exe. The nscontrol application can be used with various parameters
to administer a SQL Server 2005 Notification Services instance.
After you've completed a backup for your Sales server to disk, you want to
be sure that this backup is intact and able to be used for restores before
writing it to tape. What command will help you here?
RESTORE VERIFYONLY. After a backup file has been written to disk or tape,
its integrity can be checked with the RESTORE VERIFYONLY command. This
command will verufy the backup set is complete and that the files are readable.
Out of the box Report Builder supports two report level fields that can be
shown on a report. Which option below has those two options?
The current filter and the number of rows that matched that filter
By default, the current filter definition and the number of rows that matched the
filter are added to the end of the report. They can be removed and added back as
needed.
Can a particular event in SQL Server 2005, such as the CREATE USER
command, have more than one DDL trigger assigned to it?
Yes. An event can have multiple triggers assigned to it.
You accidently delete an application from an instance of SQL Server 2005
Notification Services. However you have not removed the database, nor the
application objects. Can you re-associate the application with the same
SSNS instance?
No. You cannot re-assocaite the application because when you add the
application, SSNS recreates the objects. If they already exist, the create fails.
How many users can be added to an application role in SQL Server 2005?
None. This is a trick questions. No users are added to application roles.
Application roles are invoked by a user.
SQL Server Interview Questions
http://marancollects.blogspot.com 10/35
What algorithm is used to encrypt the Database Master Key when it is
created?
Triple DES. When you create a Database Master Key, it is encrypted using the
password you supply with the Triple DES algorithm.
Which protocols support Kerberos authentication on SQL Server 2005?
TCP/IP. Only those clients connecting with TCP/IP can use Kerberos
authentication.
You have a few new SQL Server 2005 server instances and you want to be
sure that SQL authenticated logins must abide by the password policy. On
this platforms can you enforce this?
Windows 2003 Server. You can only enforce password policy on the Windows
2003 Server platform and newer.
You are setting up a native XML web service on your SQL Server 2005 to
respond to inventory requests. How can you you be sure that a SOAP
queries that are looking for a WSDL response will be provided?
Use the WSDL=DEFAULT parameter in the create endpoint statement.
When using the CREATE ENDPOINT command, you can specify the WSDL=DEFAULT
parameter to generate a default WSDL response or use WSDL="spname" where
spname is the name of a custom stored procedure to return WSDL responses.
Where can you view the list of server-scoped DDL triggers?
The Object Explorer for the server has a "Triggers" folder.
The server-scoped DDL triggers will appear in Management Studio in the Object
Explorer under the "Triggers" folder.
You want to be sure that your reporting solutions using a database snapshot are
properly protected from disaster. How can you back up your database snapshots?
You cannot back up a database snapshot.
A database snapshot cannot be backed up or restored.
When you install SQL Server 2005 and create a new database, is a Database Master
Key created?
No
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 2005?
Remove a reference to a cursor.
SQL Server Interview Questions
http://marancollects.blogspot.com 11/35
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
source control
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 schedule.
Setting the TRUSTWORTHY database property to ON allows you to execute the
following from within the context of the database
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
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?
128-bit AES
While all of these are valid encryption algorithms on SQL Server 2005, the AES
SQL Server Interview Questions
http://marancollects.blogspot.com 12/35
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 2003.
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 command.
This returns the current value of the TEXTSIZE option that can be changed with the
SET command. The default is 4096 bytes.
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 2005?
This statement is used to setup new tasks for Service Broker queues.
This statement is ue to create a new Service Broker service that is a set of tasks that
are run on messages.
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 creation. Why?
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.
SQL Server Interview Questions
http://marancollects.blogspot.com 13/35
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 Server 2005?
HTTP endpoints
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 subscriber.
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:
<replacement>
<pat>SS2K5 </pat>
<sub>SQL Server 2005 </sub>
<sub>SQL Server 2K5 </sub>
</replacement>
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.
SQL Server Interview Questions
http://marancollects.blogspot.com 14/35
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 term.
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?
Compressed Snapshots
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?
Off
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 log.
What would happen when you execute the code below in Query Analyzer (SQL
Server 2000)
USE Northwind
GO
CREATE PROCEDURE sp_who
AS
PRINT 'SURPRISE'
GO
EXECUTE sp_who
SQL Server Interview Questions
http://marancollects.blogspot.com 15/35
Information about current SQL Server users and processes is displayed.
The explanation could be found in the Books Online under Creating a Stored
Procedure. One of the sections, named System Stored Procedures which describes
how SQL Server looks up the system stored procedure has this note: Important If
any user-created stored procedure has the same name as a system stored
procedure, the user-created stored procedure will never be executed.
How is Thesaurus data configured in SQL Server 2005 Full-Text Search?
An XML file in the file system named tsxxx.xml where xxx is a code.
The thesaurus file for SQL Server 2005 is an XML file containing data and stored in
SQL_Server_install_pathMicrosoft SQL ServerMSSQL.1MSSQLFTDATA directory.
There is one for each language and it is named tsxxx_.xml, where xxx is the three
letter language code.
What does a report model provide in SQL Server 2005 Reporting Services?
A report model provides familiar business names for database tables and fields
A Report model provides business names and terms for database fields and tables. It
allows for predefined relationships between tables as well as grouping items together
logically.
What can tracer tokens measure in SQL Server 2005 transactional replication?
1 and 3
Tracer tokens measure two things. One is the amount of time elapsed between a
command being committed on the publisher and being written to the distribution
database. The other is from the writing to the distribution database and being
committed to a subscriber. This allows you to determine the latencies for
transactions moving through your replication topology.
You want to script the execution of an Integration Services package from the
command line for use from a Unix scheduler. What utility would you use?
dtexec.exe
The dtexec.exe utility is used to configure and execute Integration Services packages
from the command line.
What does the sqlwb utility do?
Opens SQL Server 2005 Management Studio.
sqlwb.exe actually opens Management Studio and can be configured to optionally
open a solution, project, or script file when it starts.
You want to automate the installation of SQL Server 2005 using SMO for your
custom application and ensure that the encryption features are available with a
SQL Server Interview Questions
http://marancollects.blogspot.com 16/35
service master key. What method would you call to create this key?
The Regenerate method under the ServiceMasterKey object.
To create a Service Master Key in SMO, you would get a handle to the
ServiceMasterKey object under the Server object and then call the Regenerate
method.
How can you determine which Service Broker ports are being used on your server?
Query the system catalog view: sys.conversation_endpoints
The system catalog view sys.conversation_endpoints will show you which Service
Broker endpoints, and therefore ports, are open on your server.
In Full-Text Search, what is word breaking?
Determining word boundaries.
Word breaking involves finding the boundaries of a word. This is different for
different languages and SQL Server 2005 includes word breakers for 23 languages.
Can you call Notification Services APIs from unmanaged code?
Yes, but only for simple subscriptions.
Notification Services can be called from unmanaged code through a COM interface
for simple subscriptions only. Condition based subscriptions are not supported
through COM Interop.
Which of the following is true about the Raw File Source in SQL Server 2005
Integration Services?
It does not support an error output.
The raw file source has only one output and does not support an error output. It also
reads faster than other data sources because it has no parsing or translation and
does not use a connection manager.
You wish to configure event logging for your SQL Server 2005 Notification Services
instance. Where would you make this change?
Edit the nsservice.exe.config file in the C:Program FilesMicrosoft SQL
Server90NotificationServicesn.n.nnnbin folder.
Event logging and most Notification Services configuration require editing an XML
file. In this case, the NSservice.exe.config file is edited to set the appropriate editing
level.
Which of the following is not true about the Raw File Destintion connection in SQL
Server 2005 Integration Services?
It supports BLOB object data.
The Raw File Destination connection does not use a connection manager, supports
SQL Server Interview Questions
http://marancollects.blogspot.com 17/35
NULL data, and only has one input. It also does not support BLOB data or have an
error output.
What message types exist in SQL Server 2005 Service Broker?
These are defined for each contract.
Each application that sets up queues and contracts inside Service Broker must define
the message types that are valid for the contract.
What does the CEILING() function do?
Returns the smallest integer greater than or equal to the value passed in.
CEILING() returns the smallest integer that is great than or equal to the value
passed in.
What is a dialog conversation in the SQL Server 2005 Service Broker.
A dialog conversation is a conversation between services.
A dialog conversation is a conversation between services. A conversation includes
messages being passed back and forth as part of a contract.
What is row versioning in SQL Server 2005?
Row versioning keeps a copy of each row for use by applications or transactions to
prevent readers from being blocked by writers.
Row versioning is a method whereby the database engine keeps a copy of a row's
data as it existed before the start of a transaction for queries to read this data and
reduce locking contention if they are configured.
What does @@MAX_PRECISION return?
The maximum precision for numeric and decimal data.
This function returns the maximum precision for numeric and decimal data as set on
the server. The default for SQL Server 2005 is 38.
Which of the following columns can be indexed with SQL Server 2005 Full-Text
Search?
char, varchar, nvarchar, and varbinary, text, ntext, and image
All character columns, char, varchar and nvarchar columns including max, text and
ntext, and image columns are valid for full-text searching.
When starting SQL Server 2005 from the command line, what does the -h switch do?
This switch reserves memory space for Hot-Add memory metadata, available with
AWE enabled.
This switch is used with 32-bit SQL Server and AWE to reserve memory to reserve
memory space for Hot-Add memory metadata.
SQL Server Interview Questions
http://marancollects.blogspot.com 18/35
Janice has two tables, Employees and Orders. She has been asked to provide a
report of the number of orders processed by each employee in the month of June. If
an employee didn’t process any orders, the report should reflect the employee’s
name and a zero for the number of orders. Which of the queries is the best one for
Janice to use to return the information she has been requested to provide?
SELECT
E.LastName + ', ' + E.FirstName AS [Employee Name]
, ISNULL(O.[# of Orders], 0) [# of Orders]
FROM dbo.Employees E
LEFT JOIN (SELECT
EmployeeID
, COUNT(*) [# of Orders]
FROM dbo.Orders
WHERE OrderDate >= '20060601'
AND OrderDate < '20060701'
GROUP BY EmployeeID) O
ON E.EmployeeID = O.EmployeeID
ORDER BY [Employee Name]
While it would seem BETWEEN would save Janice a bit of typing, there is a problem
with it. BETWEEN corresponds, based on these queries to the same as:
WHERE OrderDate >= ‘20060601’ AND OrderDat e<= ‘20060701’
It’s the latter one that causes the query to be incorrect as it would potentially include
orders placed on July 1st at midnight (20060701 corresponds to July 1, 2006 at
00:00:00). While this would be unlikely in a small retail environment, it is entirely
possible in larger operations, especially those which are international in scope.
Therefore, the use of BETWEEN in this case is not appropriate.
With respect to the GROUP BY, it must appear in the subquery on the Orders table,
as given in the answer. When it occurs after the LEFT JOIN, the rows where there is
an employee but no order for the month will be lost. Since Janice must report on
employees who had no sales, this is unacceptable.
In SQL Server 2005, you want to monitor your transactional replication and
determine how long it takes for a transaction to move from the publisher to a
SQL Server Interview Questions
http://marancollects.blogspot.com 19/35
subscriber. What would you use to help you do this?
Tracer Tokens.
You can use tracer tokens in SQL Server 2005 replication to measure latencies.
These are small amounts of data written into your transaction logs.
How many credentials can one login be mapped to?
1
A credential can be mapped to many logins, but a login can only be mapped to one
credential.
You are looking to secure your publisher to prevent unauthorized logins from
accessing the data through replication. Where do you add the list of logins that have
access?
Add them to the Publication Access List in the publication database.
All access to publications is controlled with the Publication Access List (PAL), which is
similar to the Windows ACLs. A list is created for each publication that is set up.
What does the ERROR_PROCEDURE() function return?
The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH
construct and returns the name of the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used to access what?
Resources outside the SQL Server
A credential is used to access resources outside of SQL Server. It contains the
authentication information needed for accessing these resources.
Which of the following is an invalid file name for a database file?
None of the above
Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file
name extensions, but these extensions help you identify the different kinds of files
and their use.
What does @@options return?
The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection.
This is returned as integer and each bit in the integer represents an option.
You have setup forwarding for your SQL Server 2005 Service Broker queues to
another server. You edit a route to move a queue to a new server. What do you need
to do now to ensure you can recover the routes in the event of a disaster?
SQL Server Interview Questions
http://marancollects.blogspot.com 20/35
Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb
database, the routes will be captured.
You are looking to setup a large Notification Services application and you are
planning on a 40GB database. When installing Notification Services, the creation
fails. What is the likely problem?
Notification Services installation has a 10 minute timeout and the database creation
probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the
database takes longer to create, the installation of the instance fails. You need to
reduce the size of the database for installation and then increase it when you are
done.
You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM
on one of your servers. An ASP.NET application on the same server is feeling
memory pressure and you want to release some memory and reduce SQL Server to
3GB or RAM. How can you do this?
Change the max amount of memory allocated and shut down and restart SQL Server
to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server
is restarted.
You are troubleshooting a Service Broker application and find that messages are
remaining on the queue. You determine that there is no active queue monitor. What
should you do?
Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an
active queue monitor. If one is not on, then it needs to be activated. The ALTER
QUEUE statement is used to change this.
You are implementing replication across the Internet for a large bank that wishes to
move up to date pricing information to an analyst's laptop. They are concerned about
spoofing of their main site by criminals. What can you do to secure the replication
solution with SQL Server 2005?
Set the encryption level to verify the certificate is issued by a trusted authority.
Since you are asked to use replication, a replication solution is to use encryption for
the connection and verify the certificate being used is from a trusted authority.
Setting the encrpytion level to 2 will do this.
SQL Server Interview Questions
http://marancollects.blogspot.com 21/35
In SQL Server 2005, you are looking to implement full-text search. One of the tables
you are looking to index stores Mircrosoft Word documents in a varbinary(max)
column. Can you use Full-text search to index this column?
Yes
You can use full-text search for formatted data such as Word that contains text
stored in a varbinary column.
To recover an instance of Notification Services in 2005 on another server, what
information would you need?
A backup of the database holding Notification Services and the XSD and XSLT files as
well as the name and password for the service account.
To recover SSNS, you need the database backup as well as the operational files, XSD
and XSLT files, stored on the file system and the account information used for the
service account. Since SSNS usually works with data outside of the SQL Server, a
domain account is usually used to ensure proper permissions.
What type of connectivity does the readpipe/makepipe utility test?
Named Pipes
The readpipe and makepipe utility combination will test named pipe connectivity.
In SQL Server 2005, which of the following schema changes are supported for the
publication objects of a replicated database?
All of the above.
SQL Server 2005 replicated databases support the following schema changes for
objects:
* ALTER TABLE
* ALTER VIEW
* ALTER PROCEDURE
* ALTER FUNCTION
* ALTER TRIGGER (DML only)
You want to disable the receipt of messages in one of your databases by the Service
Broken. How should you do this?
ALTER DATABASE Sales SET DISABLE_BROKER
To disable the receipt of messages, you can disable the Service Broker by disabling it
with the ALTER DATABASE command. The SET command is "DISABLE_BROKER".
You are building a .NET assembly that will access the registry of the local machine
for a factor used in a computer column. What permission set should you assign it?
EXTERNAL_ACCESS
SQL Server Interview Questions
http://marancollects.blogspot.com 22/35
The most restrictive permission set should always be used for .NET assemblies in
keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and
UNSAFE permission sets will allow registry access and UNSAFE permissions are not
required.
In SQL Server 2005, you want to monitor your transactional replication and
determine how long it takes for a transaction to move from the publisher to a
subscriber. What would you use to help you do this?
Tracer Tokens.
You can use tracer tokens in SQL Server 2005 replication to measure latencies.
These are small amounts of data written into your transaction logs.
How many credentials can one login be mapped to?
1
A credential can be mapped to many logins, but a login can only be mapped to one
credential.
You are looking to secure your publisher to prevent unauthorized logins from
accessing the data through replication. Where do you add the list of logins that have
access?
Add them to the Publication Access List in the publication database.
All access to publications is controlled with the Publication Access List (PAL), which is
similar to the Windows ACLs. A list is created for each publication that is set up.
What does the ERROR_PROCEDURE() function return?
The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH
construct and returns the name of the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used to access what?
Resources outside the SQL Server
A credential is used to access resources outside of SQL Server. It contains the
authentication information needed for accessing these resources.
Which of the following is an invalid file name for a database file?
None of the above
Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file
name extensions, but these extensions help you identify the different kinds of files
and their use.
SQL Server Interview Questions
http://marancollects.blogspot.com 23/35
What does @@options return?
The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection.
This is returned as integer and each bit in the integer represents an option.
You have setup forwarding for your SQL Server 2005 Service Broker queues to
another server. You edit a route to move a queue to a new server. What do you need
to do now to ensure you can recover the routes in the event of a disaster?
Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb
database, the routes will be captured.
You are looking to setup a large Notification Services application and you are
planning on a 40GB database. When installing Notification Services, the creation
fails. What is the likely problem?
Notification Services installation has a 10 minute timeout and the database creation
probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the
database takes longer to create, the installation of the instance fails. You need to
reduce the size of the database for installation and then increase it when you are
done.
You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM
on one of your servers. An ASP.NET application on the same server is feeling
memory pressure and you want to release some memory and reduce SQL Server to
3GB or RAM. How can you do this?
Change the max amount of memory allocated and shut down and restart SQL Server
to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server
is restarted.
You are troubleshooting a Service Broker application and find that messages are
remaining on the queue. You determine that there is no active queue monitor. What
should you do?
Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an
active queue monitor. If one is not on, then it needs to be activated. The ALTER
QUEUE statement is used to change this.
SQL Server Interview Questions
http://marancollects.blogspot.com 24/35
You are implementing replication across the Internet for a large bank that wishes to
move up to date pricing information to an analyst's laptop. They are concerned about
spoofing of their main site by criminals. What can you do to secure the replication
solution with SQL Server 2005?
Set the encryption level to verify the certificate is issued by a trusted authority.
Since you are asked to use replication, a replication solution is to use encryption for
the connection and verify the certificate being used is from a trusted authority.
Setting the encrpytion level to 2 will do this.
In SQL Server 2005, you are looking to implement full-text search. One of the tables
you are looking to index stores Mircrosoft Word documents in a varbinary(max)
column. Can you use Full-text search to index this column?
Yes
You can use full-text search for formatted data such as Word that contains text
stored in a varbinary column.
To recover an instance of Notification Services in 2005 on another server, what
information would you need?
A backup of the database holding Notification Services and the XSD and XSLT files as
well as the name and password for the service account.
To recover SSNS, you need the database backup as well as the operational files, XSD
and XSLT files, stored on the file system and the account information used for the
service account. Since SSNS usually works with data outside of the SQL Server, a
domain account is usually used to ensure proper permissions.
What type of connectivity does the readpipe/makepipe utility test?
Named Pipes
The readpipe and makepipe utility combination will test named pipe connectivity.
In SQL Server 2005, which of the following schema changes are supported for the
publication objects of a replicated database?
All of the above.
SQL Server 2005 replicated databases support the following schema changes for
objects:
* ALTER TABLE
* ALTER VIEW
* ALTER PROCEDURE
* ALTER FUNCTION
* ALTER TRIGGER (DML only)
SQL Server Interview Questions
http://marancollects.blogspot.com 25/35
You want to disable the receipt of messages in one of your databases by the Service
Broken. How should you do this?
ALTER DATABASE Sales SET DISABLE_BROKER
To disable the receipt of messages, you can disable the Service Broker by disabling it
with the ALTER DATABASE command. The SET command is "DISABLE_BROKER".
You are building a .NET assembly that will access the registry of the local machine
for a factor used in a computer column. What permission set should you assign it?
EXTERNAL_ACCESS
The most restrictive permission set should always be used for .NET assemblies in
keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and
UNSAFE permission sets will allow registry access and UNSAFE permissions are not
required.
What What does the term ACID refer to?
An acronym that describes concurrency support in a database
The answer is an acronym that describes concurrency support in a database. ACID is
an acronym for the following four terms: * Atomicity - Every operation within a
transaction is treated as a singular operation; either all of its data modifications are
performed, or none of them is performed.
* Consistency - Once the transaction is completed, the system must be left in a
consistent state.
* Isolation - It means that the operations within a transaction must be suitably
isolated from other transactions. In other words, no other transactions should see
data in the intermediate state, within the transaction, until it is finalized. This is done
by using locks.
* Durability - Once a transaction is competed, all changes must be persisted as
requested. The modifications should persist in total even in the event of a system
failure.
What does this return?
SELECT USER_NAME() -- Returns Andy
EXECUTE AS TechPreparation
GO
EXECUTE AS Steve
GO
REVERT
GO
SQL Server Interview Questions
http://marancollects.blogspot.com 26/35
SELECT USER_NAME()
TechPreparation
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?
png
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 two tables.
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.)
11-16
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?
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 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?
2
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
SQL Server Interview Questions
http://marancollects.blogspot.com 27/35
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 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?
Full
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?
Yes
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?
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 database user?
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.
SQL Server Interview Questions
http://marancollects.blogspot.com 28/35
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?
varchar(max)
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 the table.
Yesterday's QOD How are comments denoted in an XML document?
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 views?
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 problem?
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 fashion?
Like a queue, first in, first out.
SQL Server Interview Questions
http://marancollects.blogspot.com 29/35
Messages transferred in Service Broker work in a queue fashion with the first
message being sent being the first processed.
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?
db_accessadmin
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 logins.
What does this method in SQL Server 2005's SMO equate to in T-SQL?
db.CheckTables(RepairType.None)
DBCC CHECKDB WITH NO_INFOMSGS
This SMO method executes a DBCC CHECKDB with the NO_INFOMSGS option set.
In SQL Server 2005, can you include a user-defined role as a member of another
user-defined role?
Yes
Yes you can.
In SQL Server 2005, what does instance aware mean for a service?
The service is installed once for each instances on the server.
An instance aware service is one that is installed for each instance on the server. An
instance-unaware service is only installed once , no matter how many instances.
Integration Services is instance-unaware whereas SQL Agent is instance aware.
If you use the EXECUTE AS clause to impersonate a user at a database-scoped level.
Can you execute a query through a linked server if the impersonated user has the
rights to do so when normally logged in? No trust relationships exist between the
databases.
No
If you change your execution context to a user, which is scoped at a database level,
then any queries to linked servers or other databases will fail. There is a way around
this restriction if trust relationships are setup.
The nesting level for stored procedures in SQL Server 2005, but how many
databases can you access inside one stored procedure?
SQL Server Interview Questions
http://marancollects.blogspot.com 30/35
8
The footnote for nested stored procedures limits the number at 8.
Is it possible to build an index on a view?
Yes
Yes, an index can be put on a view but the first index must be a unique clustered
index.
What does REVERT do in SQL Server 2005?
Restores your previous execution context.
If you have changed your execution context with EXECUTE AS, the REVERT
statement will restore the last context prior to the EXECUTE AS.
What does SEND do in SQL Server 2005?
Sends a service broker message using a conversation.
SEND is used to send a message on an existing conversation in the Service Broker
architecture.
In SQL Server 2005, most data for your data types is stored in the pages for the
table. Binary objects, such as image or text, are stored in LOB or large Object data
pages. Where is varchar(max) data stored?
In LOB pages
Since the size restrictions for varchar(max) are the same as for text data, this data
is stored in LOB data pages.
What will occur with the following code in SQL Server 2005 if executed by Alice?
execute as user = 'Bob' with no revert
select user_name()
go
revert
select user_name()
go
You will receive as results:
bob
An error
The NO REVERT option with EXECUTE AS prevents the return of execution context to
SQL Server Interview Questions
http://marancollects.blogspot.com 31/35
the previous value. If you run the REVERT statement, you will receive the following
error:
You have to create some T-SQL that produces an order of players in a golf
tournament. Players who finish on the same number of strokes have to be given the
same finishing place denoting that they will have an equal prize amount. What T-SQL
function can achieve this?
RANK alongside the OVER function where the OVER function uses the players
number of strokes taken
The rank function will return the rank of the row in the result set. Ties will receive
the same rank. The OVER clause will separate out the ranking into partitions and use
those for calculating the proper placement of the golfers.
You want to disable the trigger dEmployee on the HumanResources.Employee table
in the SQL Server 2005 AdventureWorks sample database. Which of the following
statements will do the trick?
Answer
A and B
Explanation
Either of the commands in A and B will disable the trigger.
In SQL Server 2005, if you want the keys securing your data to expire over time,
what type of key encryption would you choose?
Answer
Certificates
Explanation
Certificates have an expiration data associated with them. This allows you to issue
them to users and force a reissue in order to maintain their access to data.
Which is larger in size in SQL Server 2005 if you are using symmetric keys?
SQL Server Interview Questions
http://marancollects.blogspot.com 32/35
Answer
Ciphertext
Explanation
Ciphertext is larger and can be calculated using the following formula:
Size = ( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK + 16)
where D is the data size in bytes, BLOCK is the block size (8 or 16, depending on
algorithm) and Size is the new size in bytes of the ciphertext.
What result would you expect from this statement?
Select * from dbo
.MyTable
All records from [dbo].[MyTable]
The answer is all records from [dbo].[MyTable]. Qualified names can include white
space before and after the dots. Restrictions apply only to the names themselves
that may need quoted identifiers with embedded white space or invalid characters.
You are using SQL Server 2005 and you wish to find a number of details about
stored procedures created. The details include date and time, the server that created
the stored procedure, the login name and the contents. How would you do this?
Create a DDL trigger on CREATE_PROCEDURE database event
The answer is Create a DDL trigger on CREATE_PROCEDURE database event You can
find the answer on page 448 of Beginning SQL Server 2005 for Developers: From
Novice to Professional
A .NET assembly running with UNSAFE permissions can do which of the following?
All of the above
CLR code running with UNSAFE permissions can access virtually any resource inside
or outside of SQL Server without restriction. This is one reason only a sysadmin can
create an assembly as UNSAFE.
What does the SQL Writer service do?
Handles Volume Shadow Service Copy functions.
The SQL Writer service was introduced to handle access to the data files in SQL
Server. It allows backup programs, like VSS, to function while SQL Server is still
running.
Which key provides the strongest encryption?
AES (256 bit)
SQL Server Interview Questions
http://marancollects.blogspot.com 33/35
The longer the key, the better the encryption, so choose longer keys for more
encryption. However there is a larger performance penalty for longer keys. DES is a
relatively old and weaker algorithm than AES.
You are delegating permissions on your SQL Server 2005 server to other
administrators. You have local, single server jobs on one server that you would like
to allow another administer to start, stop, and view the history for, but not delete
history. This administrator will own the jobs. Which role should you assign?
SQLAgentUserRole
SQL Server 2005 provides 3 fixed roles for the agent service that limit privileges for
administrators. The SQLAgentUserRole is designed for local jobs (not multiserver)
that allows the member to work with their owned jobs (edit, start, stop, view
history) without deleting the history of any job.
What level of permissions does a user need to create an assembly with UNSAFE
permissions?
sysadmin
Only a sysadmin can create assemblies that are specified as unsafe.
Which optional SQL Server component must you install to ensure that you can create
and execute maintenance plans?
SQL Server Integration Services
You must install Integration Services in order to properly create and execute
maintance plans since these plans are a subset of SSIS packages.
You have tightly secured access to your SQL Server 2005 server by placing it behind
a firewall and limited ports 1433 to those clients that need to directly access the SQL
Server. One of your developers complains about connecting to the Integration
Services esrvice on your server. What could be the problem if you know the service
is working properly?
The firewall needs to allow port 135 through for Integration Services
Integration Services requires access to both DCOM and port 135. You need to open
this port to allow the developer to communicate with SSIS.
In SQL Server 2005, which network protocols use endpoints?
All of the above
All communication points with SQL Server using network protocols use an endpoint.
SQL Server creates defaults for each protocol that you enable.
You have a SQL Server 2005 cluster and need to add anti-virus software as per your
corporate standards. What should you exclude from scans?
SQL Server Interview Questions
http://marancollects.blogspot.com 34/35
Log and data files for your databases as well as the Quorum drive and the backup
folders.
Anti-virus programs can exist on the same server as SQL Server without an issue if
you exclude certain items. The database data and log files, backup files and folders,
the quorum drive for clusters, temporary replication files, the SQL Server log files,
and Log Shipping folders/files. You do not need to exclude that SQL Server
executables and probably do not want to so that they are protected.
What port do you need to open on your server firewall to enable named pipes
connections?
Port 445
Named pipes communicates across TCP port 445.
When the network service built-in account connects to a network resource, what is
the context?
The computer account of the Windows installation.
The network service account connects to network resources as the computer account
for the Windows installation.
A certificate is like which type of key in SQL Server 2005 encryption?
An Asymmetric Key
A certificate is like an asymmetric key and can validate the identity of the issuer.
How many connections are available in SQL Server MSDE 2000?
Unlimited, no different from the other SQL Server 2000 editions.
The answer is actually an unlimited number of connections, though this in a practical
sense is limited by memory on the machine (The actual limitation is 32k
connecitons). Instead there is a governer that kicks in to limit things to 8 operations,
of which 3 are system operations in some cases. But these are operations, not
connections.
What will be the result of the last SELECT statement if you execute the T-SQL script
below as a sysadmin.
USE master
GO
IF DB_ID('test_schema') IS NOT NULL
DROP DATABASE test_schema
GO
CREATE DATABASE test_schema
GO
SQL Server Interview Questions
http://marancollects.blogspot.com 35/35
USE test_schema
GO
CREATE SCHEMA Schema1
CREATE TABLE Table1 (col1 int)
GO
CREATE SCHEMA Schema2
GO
CREATE TABLE Table1 (col1 int)
GO
SELECT SCHEMA_NAME(schema_id), name FROM sys.objects
WHERE name = 'Table1'
Schema1 Table1
dbo Table1
The correct answer is A. You can combine the CREATE SCHEMA with a CREATE
TABLE. You can even specify GRANTs or DENYs. Search Books online for CREATE
SCHEMA.
SQL Server is a DBMS system provided by Microsoft. SQL Server is
sometimes mistakenly referred to as SQL.
Error severity 13 indicates what?
Transactional deadlock error. This level of error severity indicates a
transaction deadlock error.
In which order do you perform an upgrade to SQL Server 2005 for replicated
databases?
Distributor, Publisher and then Subscriber. You always perform an upgrade in
this order: distributor, publisher, subscriber.
How many Service Packs will be released for SQL Server 2005 in 2007?
Explanation: The answer is up in the air and this is more of a poll than a real
QOD. Based on the ways things are going, the staff here sees just 1, though our
hope would be that 3 or 4 would be released.
You setup a linked server from a SQL Server 2000 server to your new SQL
Server 2005 server (with defaults), however you cannot execute procedures
on the 2005 server. Why not?
You need to enable RPC. By default, RPC is disabled in SQL Server 2005. You
need to set the "remote access option" in your server configuration to 1 to allow the
execution of stored procedures from a remote server.
What is the recommended way to send mail from SQLAgent in SQL Server
2005?
Database Mail. You can use either Database Mail or SQLMail with SQL Agent
in SQL Server 2005. However since SQLMail will be removed, it is recommended that
you use Database Mail.
When you create a new Message Type in the SQL Server 2005 Service
Broker, what does the Authorization parameter signify?
The owner of the message type. This parameter determines the owner of the
message type. This defaults to the current user.
What the heck does ATN2 do?
The angle between the x-axis and a ray. This is a mathematical function that
returns the angle between the positive x-axis and the ray that passes through the
two coordinates passed in. The angle is in radians.
SQL Server Interview Questions
http://marancollects.blogspot.com 2/35
How does a differential backup know which extents have changed so that it
can be very quickly run?
The DCM tracks changes. The differential backup reads the extents from this
structure. A differential backup uses the Differential Change Map to determine which
extents have changed and need to be include in the backup. This greatly speeds the
differential backup process.
What does the Queue Reader Agent do in SQL Server 2005 replication?
This agent reads the subscriber logs and moves changes back to the
publisher. This agent is used when the queued update model is chosen with
transactional replication. It moves changes from the subscribers back to the
publishers.
What are the three possible functions of the plus (+) operator in SQL Server
2005, the base installed T-SQL?
Add, string concatenation, unary plus. The three functions are Add, String
Concatenation, and Unary Plus.
The Sort component in SQL Server 2005 Integration Services is what type of
component?
Blocking Transformation. The Sort component is a blocking transformation
meaning that it needs all rows in order to perform its function.
If you received a "Performance Critical" status in the SQL Server 2005
replication monitor, what does this mean?
The latency between transaction commits at the publisher and subscriber
exceeds the warning level. This status can actually mean two different things. Either
the latency between the commit of a transaction at the publisher and the same
commit at the subscriber is exceeding some level in a transactional level or not
enough rows are being processed in a merge replication scenario.
Which of the following modules within SQL Server 2005 cannot be signed
with a digital signature?
DDL triggers. DDL triggers cannot be signed, but all the other objects can.
What does this return?
declare @i int
select @i = -5
select +@i
-5
SQL Server Interview Questions
http://marancollects.blogspot.com 3/35
This will return -5 as the result. The + operator functions as a unary plus
operator, which means that it performs no operation on the value it preceeds.
You have installed a US English SQL Server 2000 instance with the default
options, collation, and sorting. What does this return?
create table MyTable ( Mychar varchar(20))
go
insert Mytable select 'Apple'
insert Mytable select 'ant'
insert Mytable select 'Ball'
go
select * from MyTable where Mychar like '[^a]%' Ball
This should return "Ball" only since the ^ operator means not matching the
next character. In this case, the first character should not be an "A".
What is the Service Broker Identifier?
A GUID that identifies the database on which Service Broker is running. Each
database has a Service Broker identifier. This is a GUID in the service_broker_GUID
column that identifies the databases on which Service Broker is running. It ensure
that messages are delivered to the right database.
You are looking to import a large amount of data from a remote OLEDB data
source that is not a text file. Which of the following techniques can you use?
Use the select * from OPENROWSET(BULK...) command. SQL Server 2005
includes a new option with the OPENROWSET command for getting large amounts of
data from an OLEDB data source. It is the BULK option and works similar to the
BULK INSERT command.
How are modified extents tracked in SQL Server 2005 (which internal
structures)?
Differential Change Map and Bulk Change Map. There are two internal
structures that track extents modified by bulk copy operations or that have changed
since the last full backup. They are the Differential Changed Map (DCM) and the Bulk
Changed Map (BCM).
What does this return? select (1324 & 1024)
1024. This performs a bitwise AND operation between the two integers and
sets the result to this. Since 1024 is a single set bit in it's value, if the corresponding
bit is set to 1, then in the result the bit is set to 1. In this case, since no other bits
would generate two 1s, the result is equivalevt to the mask, or 1024.
SQL Server Interview Questions
http://marancollects.blogspot.com 4/35
What does the Log Reader agent in SQL Server 2005 replication do?
This agent reads the publisher log for transactions to send to the distributor.
This agent is tasked with reading the transaction log in transactional replication and
moving those transactions that need to be replicated to the distributor.
You are performing an update of your Scalable Shared Database and receive
note that two reports run at the same time received different results. These
reports were both run during your update. What type of update did you
perform?
A rolling update. When performing a rolling update, doing the detach and
attach on each server as opposed to detaching from all then attaching to all, it is
possible that different reporting servers will display different results.
What does a @@fetch_status of -2 mean in SQL Server 2005?
The row being fetched is missing. This means that the row that was being
fetched from the cursor is missing.
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 Server 2005?
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 you do?
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 servers. 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.
SQL Server Interview Questions
http://marancollects.blogspot.com 5/35
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 programmatically?
SQL-DMO. 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 crawl.
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 Server 2005?
TEXT_IN_ROW_DATA. 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 sense.
How can SQL Server Agent Mail send messages in SQL Server 2005?
SQL Server Interview Questions
http://marancollects.blogspot.com 6/35
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?
Seconds. This value measures the number of seconds for a plan where the
optimizer chooses between serial and parallel plans.
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 time parameters?
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 filtered.
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 choice?
TIFF. 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 options?
SQL Server Interview Questions
http://marancollects.blogspot.com 7/35
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 Report Builder
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 level.
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.
Using Report Builder, which of the following is the best statement about the
formatting options for Boolean columns?
Booleans are formatted as True/False and there are no other built in options,
but you could build an expression using IIF that would let you do other formatting
From the format dialog there are no extra formatting options for Booleans, Report
Builder renders them as True/False. Writing an expression that you add to the model
view is the easiest way to work around this limitation.
What is the easiest way to capture the SQL statement for a Report Builder
report you're troubleshooting?
Run Profiler. Profiler will work as long as you have permissions to profile the
server and is the the best solution because it requires no change to the Report
Server itself. There is a way to log all report SQL to a log file, but that option was not
listed here and is better used if you want to do analysis rather than troubleshooting.
Clicking File, Save in Report Builder does which of the following?
Saves the report to the report server. File|Save writes the report to the
Report Server. Users have the option to also save the report to disk by using
File|Save to File. Report Builder users cannot modify the model.
Which of the following choices show the three report formats supported by
Report Builder?
Table, Matrix, Chart. Report Builder can build a report formatted as a table,
chart, or matrix (cross tab), but only ONE can be used in any given report.
Using Report Builder, which of the following statements is correct about
formatting numbers?
SQL Server Interview Questions
http://marancollects.blogspot.com 8/35
Users can pick from a small number of predefined formats and they have the
option to specify a custom format. There are give built in formats; general, currency,
percentage, two place decimal, and exponent. Users can also define a custom format
using a .Net format string.
True or false, Report Builder supports using the LIKE function inside filters?
False. There is no LIKE support, the next best thing is the CONTAINS function
which works as if you specified both a leading and trailing wild card.
Which RAID levels store parity information?
RAID 5. Only RAID 5 (of those listed) contains parity information.
You have a large table that you wish to partition to improve performance.
The table contains many columns of data about customers and you decide
that basic information about each customer will remain in the current table.
Extended information, such as shipping instructions, secretaries' names,
etc. will be moved to a new table along with the PK. What type of
partitioning is this?
Vertical partitioning. If you are moving some columns from one table to a new
table, this is vertical partitioning.
On which platforms can you use Instant File Initialization to improve
database growth performance in SQL Server 2005?
Windows 2003 and XP Pro. Both Windows 2003 Server and later as well as
Windows XP Professional support Instant File Initialization.
You have created a database snapshot on SQL Server 2005 for the sales
database to capture the end-of-month activity. The next day your server
fails and you need to recover to a standby server using the previous night's
backups. How do you recover the snapshot?
There is nothing you can do. The snapshot is lost. Database snapshots cannot
be backed up, so once the server failed, the database snapshot was lost.
Using Reporting Services 2005, it is true or false that subreports execute a
query against the datasource once for every detail row in the report?
True. Subreports can be used for a master-detail relationship, or the
subreport can be a separate item, but in either case RS will query to get the data for
the report once for each detail row. If end users are going to only occasionally look
at the data you're displaying in the subreport or only view it for a few rows, a better
option is to create a link to the other report.
SQL Server Interview Questions
http://marancollects.blogspot.com 9/35
You have noticed in both your SQL Server 2000 and 2005 instances that
when a database grows in SQL Server, there is a delay in the database
response. Why is that?
Once the file is grown, zeros are written to the new space, causing a delay.
When a database file grows, unless instant file initialization is turned on, the server
must allocate disk space and then write 0s into all that space. This zero-ing out of
the file creates the delay.
Which utility is used to administer SQL Server 2005 Notification Services
instances?
nscontrol.exe. The nscontrol application can be used with various parameters
to administer a SQL Server 2005 Notification Services instance.
After you've completed a backup for your Sales server to disk, you want to
be sure that this backup is intact and able to be used for restores before
writing it to tape. What command will help you here?
RESTORE VERIFYONLY. After a backup file has been written to disk or tape,
its integrity can be checked with the RESTORE VERIFYONLY command. This
command will verufy the backup set is complete and that the files are readable.
Out of the box Report Builder supports two report level fields that can be
shown on a report. Which option below has those two options?
The current filter and the number of rows that matched that filter
By default, the current filter definition and the number of rows that matched the
filter are added to the end of the report. They can be removed and added back as
needed.
Can a particular event in SQL Server 2005, such as the CREATE USER
command, have more than one DDL trigger assigned to it?
Yes. An event can have multiple triggers assigned to it.
You accidently delete an application from an instance of SQL Server 2005
Notification Services. However you have not removed the database, nor the
application objects. Can you re-associate the application with the same
SSNS instance?
No. You cannot re-assocaite the application because when you add the
application, SSNS recreates the objects. If they already exist, the create fails.
How many users can be added to an application role in SQL Server 2005?
None. This is a trick questions. No users are added to application roles.
Application roles are invoked by a user.
SQL Server Interview Questions
http://marancollects.blogspot.com 10/35
What algorithm is used to encrypt the Database Master Key when it is
created?
Triple DES. When you create a Database Master Key, it is encrypted using the
password you supply with the Triple DES algorithm.
Which protocols support Kerberos authentication on SQL Server 2005?
TCP/IP. Only those clients connecting with TCP/IP can use Kerberos
authentication.
You have a few new SQL Server 2005 server instances and you want to be
sure that SQL authenticated logins must abide by the password policy. On
this platforms can you enforce this?
Windows 2003 Server. You can only enforce password policy on the Windows
2003 Server platform and newer.
You are setting up a native XML web service on your SQL Server 2005 to
respond to inventory requests. How can you you be sure that a SOAP
queries that are looking for a WSDL response will be provided?
Use the WSDL=DEFAULT parameter in the create endpoint statement.
When using the CREATE ENDPOINT command, you can specify the WSDL=DEFAULT
parameter to generate a default WSDL response or use WSDL="spname" where
spname is the name of a custom stored procedure to return WSDL responses.
Where can you view the list of server-scoped DDL triggers?
The Object Explorer for the server has a "Triggers" folder.
The server-scoped DDL triggers will appear in Management Studio in the Object
Explorer under the "Triggers" folder.
You want to be sure that your reporting solutions using a database snapshot are
properly protected from disaster. How can you back up your database snapshots?
You cannot back up a database snapshot.
A database snapshot cannot be backed up or restored.
When you install SQL Server 2005 and create a new database, is a Database Master
Key created?
No
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 2005?
Remove a reference to a cursor.
SQL Server Interview Questions
http://marancollects.blogspot.com 11/35
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
source control
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 schedule.
Setting the TRUSTWORTHY database property to ON allows you to execute the
following from within the context of the database
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
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?
128-bit AES
While all of these are valid encryption algorithms on SQL Server 2005, the AES
SQL Server Interview Questions
http://marancollects.blogspot.com 12/35
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 2003.
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 command.
This returns the current value of the TEXTSIZE option that can be changed with the
SET command. The default is 4096 bytes.
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 2005?
This statement is used to setup new tasks for Service Broker queues.
This statement is ue to create a new Service Broker service that is a set of tasks that
are run on messages.
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 creation. Why?
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.
SQL Server Interview Questions
http://marancollects.blogspot.com 13/35
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 Server 2005?
HTTP endpoints
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 subscriber.
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:
<replacement>
<pat>SS2K5 </pat>
<sub>SQL Server 2005 </sub>
<sub>SQL Server 2K5 </sub>
</replacement>
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.
SQL Server Interview Questions
http://marancollects.blogspot.com 14/35
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 term.
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?
Compressed Snapshots
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?
Off
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 log.
What would happen when you execute the code below in Query Analyzer (SQL
Server 2000)
USE Northwind
GO
CREATE PROCEDURE sp_who
AS
PRINT 'SURPRISE'
GO
EXECUTE sp_who
SQL Server Interview Questions
http://marancollects.blogspot.com 15/35
Information about current SQL Server users and processes is displayed.
The explanation could be found in the Books Online under Creating a Stored
Procedure. One of the sections, named System Stored Procedures which describes
how SQL Server looks up the system stored procedure has this note: Important If
any user-created stored procedure has the same name as a system stored
procedure, the user-created stored procedure will never be executed.
How is Thesaurus data configured in SQL Server 2005 Full-Text Search?
An XML file in the file system named tsxxx.xml where xxx is a code.
The thesaurus file for SQL Server 2005 is an XML file containing data and stored in
SQL_Server_install_pathMicrosoft SQL ServerMSSQL.1MSSQLFTDATA directory.
There is one for each language and it is named tsxxx_.xml, where xxx is the three
letter language code.
What does a report model provide in SQL Server 2005 Reporting Services?
A report model provides familiar business names for database tables and fields
A Report model provides business names and terms for database fields and tables. It
allows for predefined relationships between tables as well as grouping items together
logically.
What can tracer tokens measure in SQL Server 2005 transactional replication?
1 and 3
Tracer tokens measure two things. One is the amount of time elapsed between a
command being committed on the publisher and being written to the distribution
database. The other is from the writing to the distribution database and being
committed to a subscriber. This allows you to determine the latencies for
transactions moving through your replication topology.
You want to script the execution of an Integration Services package from the
command line for use from a Unix scheduler. What utility would you use?
dtexec.exe
The dtexec.exe utility is used to configure and execute Integration Services packages
from the command line.
What does the sqlwb utility do?
Opens SQL Server 2005 Management Studio.
sqlwb.exe actually opens Management Studio and can be configured to optionally
open a solution, project, or script file when it starts.
You want to automate the installation of SQL Server 2005 using SMO for your
custom application and ensure that the encryption features are available with a
SQL Server Interview Questions
http://marancollects.blogspot.com 16/35
service master key. What method would you call to create this key?
The Regenerate method under the ServiceMasterKey object.
To create a Service Master Key in SMO, you would get a handle to the
ServiceMasterKey object under the Server object and then call the Regenerate
method.
How can you determine which Service Broker ports are being used on your server?
Query the system catalog view: sys.conversation_endpoints
The system catalog view sys.conversation_endpoints will show you which Service
Broker endpoints, and therefore ports, are open on your server.
In Full-Text Search, what is word breaking?
Determining word boundaries.
Word breaking involves finding the boundaries of a word. This is different for
different languages and SQL Server 2005 includes word breakers for 23 languages.
Can you call Notification Services APIs from unmanaged code?
Yes, but only for simple subscriptions.
Notification Services can be called from unmanaged code through a COM interface
for simple subscriptions only. Condition based subscriptions are not supported
through COM Interop.
Which of the following is true about the Raw File Source in SQL Server 2005
Integration Services?
It does not support an error output.
The raw file source has only one output and does not support an error output. It also
reads faster than other data sources because it has no parsing or translation and
does not use a connection manager.
You wish to configure event logging for your SQL Server 2005 Notification Services
instance. Where would you make this change?
Edit the nsservice.exe.config file in the C:Program FilesMicrosoft SQL
Server90NotificationServicesn.n.nnnbin folder.
Event logging and most Notification Services configuration require editing an XML
file. In this case, the NSservice.exe.config file is edited to set the appropriate editing
level.
Which of the following is not true about the Raw File Destintion connection in SQL
Server 2005 Integration Services?
It supports BLOB object data.
The Raw File Destination connection does not use a connection manager, supports
SQL Server Interview Questions
http://marancollects.blogspot.com 17/35
NULL data, and only has one input. It also does not support BLOB data or have an
error output.
What message types exist in SQL Server 2005 Service Broker?
These are defined for each contract.
Each application that sets up queues and contracts inside Service Broker must define
the message types that are valid for the contract.
What does the CEILING() function do?
Returns the smallest integer greater than or equal to the value passed in.
CEILING() returns the smallest integer that is great than or equal to the value
passed in.
What is a dialog conversation in the SQL Server 2005 Service Broker.
A dialog conversation is a conversation between services.
A dialog conversation is a conversation between services. A conversation includes
messages being passed back and forth as part of a contract.
What is row versioning in SQL Server 2005?
Row versioning keeps a copy of each row for use by applications or transactions to
prevent readers from being blocked by writers.
Row versioning is a method whereby the database engine keeps a copy of a row's
data as it existed before the start of a transaction for queries to read this data and
reduce locking contention if they are configured.
What does @@MAX_PRECISION return?
The maximum precision for numeric and decimal data.
This function returns the maximum precision for numeric and decimal data as set on
the server. The default for SQL Server 2005 is 38.
Which of the following columns can be indexed with SQL Server 2005 Full-Text
Search?
char, varchar, nvarchar, and varbinary, text, ntext, and image
All character columns, char, varchar and nvarchar columns including max, text and
ntext, and image columns are valid for full-text searching.
When starting SQL Server 2005 from the command line, what does the -h switch do?
This switch reserves memory space for Hot-Add memory metadata, available with
AWE enabled.
This switch is used with 32-bit SQL Server and AWE to reserve memory to reserve
memory space for Hot-Add memory metadata.
SQL Server Interview Questions
http://marancollects.blogspot.com 18/35
Janice has two tables, Employees and Orders. She has been asked to provide a
report of the number of orders processed by each employee in the month of June. If
an employee didn’t process any orders, the report should reflect the employee’s
name and a zero for the number of orders. Which of the queries is the best one for
Janice to use to return the information she has been requested to provide?
SELECT
E.LastName + ', ' + E.FirstName AS [Employee Name]
, ISNULL(O.[# of Orders], 0) [# of Orders]
FROM dbo.Employees E
LEFT JOIN (SELECT
EmployeeID
, COUNT(*) [# of Orders]
FROM dbo.Orders
WHERE OrderDate >= '20060601'
AND OrderDate < '20060701'
GROUP BY EmployeeID) O
ON E.EmployeeID = O.EmployeeID
ORDER BY [Employee Name]
While it would seem BETWEEN would save Janice a bit of typing, there is a problem
with it. BETWEEN corresponds, based on these queries to the same as:
WHERE OrderDate >= ‘20060601’ AND OrderDat e<= ‘20060701’
It’s the latter one that causes the query to be incorrect as it would potentially include
orders placed on July 1st at midnight (20060701 corresponds to July 1, 2006 at
00:00:00). While this would be unlikely in a small retail environment, it is entirely
possible in larger operations, especially those which are international in scope.
Therefore, the use of BETWEEN in this case is not appropriate.
With respect to the GROUP BY, it must appear in the subquery on the Orders table,
as given in the answer. When it occurs after the LEFT JOIN, the rows where there is
an employee but no order for the month will be lost. Since Janice must report on
employees who had no sales, this is unacceptable.
In SQL Server 2005, you want to monitor your transactional replication and
determine how long it takes for a transaction to move from the publisher to a
SQL Server Interview Questions
http://marancollects.blogspot.com 19/35
subscriber. What would you use to help you do this?
Tracer Tokens.
You can use tracer tokens in SQL Server 2005 replication to measure latencies.
These are small amounts of data written into your transaction logs.
How many credentials can one login be mapped to?
1
A credential can be mapped to many logins, but a login can only be mapped to one
credential.
You are looking to secure your publisher to prevent unauthorized logins from
accessing the data through replication. Where do you add the list of logins that have
access?
Add them to the Publication Access List in the publication database.
All access to publications is controlled with the Publication Access List (PAL), which is
similar to the Windows ACLs. A list is created for each publication that is set up.
What does the ERROR_PROCEDURE() function return?
The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH
construct and returns the name of the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used to access what?
Resources outside the SQL Server
A credential is used to access resources outside of SQL Server. It contains the
authentication information needed for accessing these resources.
Which of the following is an invalid file name for a database file?
None of the above
Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file
name extensions, but these extensions help you identify the different kinds of files
and their use.
What does @@options return?
The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection.
This is returned as integer and each bit in the integer represents an option.
You have setup forwarding for your SQL Server 2005 Service Broker queues to
another server. You edit a route to move a queue to a new server. What do you need
to do now to ensure you can recover the routes in the event of a disaster?
SQL Server Interview Questions
http://marancollects.blogspot.com 20/35
Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb
database, the routes will be captured.
You are looking to setup a large Notification Services application and you are
planning on a 40GB database. When installing Notification Services, the creation
fails. What is the likely problem?
Notification Services installation has a 10 minute timeout and the database creation
probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the
database takes longer to create, the installation of the instance fails. You need to
reduce the size of the database for installation and then increase it when you are
done.
You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM
on one of your servers. An ASP.NET application on the same server is feeling
memory pressure and you want to release some memory and reduce SQL Server to
3GB or RAM. How can you do this?
Change the max amount of memory allocated and shut down and restart SQL Server
to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server
is restarted.
You are troubleshooting a Service Broker application and find that messages are
remaining on the queue. You determine that there is no active queue monitor. What
should you do?
Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an
active queue monitor. If one is not on, then it needs to be activated. The ALTER
QUEUE statement is used to change this.
You are implementing replication across the Internet for a large bank that wishes to
move up to date pricing information to an analyst's laptop. They are concerned about
spoofing of their main site by criminals. What can you do to secure the replication
solution with SQL Server 2005?
Set the encryption level to verify the certificate is issued by a trusted authority.
Since you are asked to use replication, a replication solution is to use encryption for
the connection and verify the certificate being used is from a trusted authority.
Setting the encrpytion level to 2 will do this.
SQL Server Interview Questions
http://marancollects.blogspot.com 21/35
In SQL Server 2005, you are looking to implement full-text search. One of the tables
you are looking to index stores Mircrosoft Word documents in a varbinary(max)
column. Can you use Full-text search to index this column?
Yes
You can use full-text search for formatted data such as Word that contains text
stored in a varbinary column.
To recover an instance of Notification Services in 2005 on another server, what
information would you need?
A backup of the database holding Notification Services and the XSD and XSLT files as
well as the name and password for the service account.
To recover SSNS, you need the database backup as well as the operational files, XSD
and XSLT files, stored on the file system and the account information used for the
service account. Since SSNS usually works with data outside of the SQL Server, a
domain account is usually used to ensure proper permissions.
What type of connectivity does the readpipe/makepipe utility test?
Named Pipes
The readpipe and makepipe utility combination will test named pipe connectivity.
In SQL Server 2005, which of the following schema changes are supported for the
publication objects of a replicated database?
All of the above.
SQL Server 2005 replicated databases support the following schema changes for
objects:
* ALTER TABLE
* ALTER VIEW
* ALTER PROCEDURE
* ALTER FUNCTION
* ALTER TRIGGER (DML only)
You want to disable the receipt of messages in one of your databases by the Service
Broken. How should you do this?
ALTER DATABASE Sales SET DISABLE_BROKER
To disable the receipt of messages, you can disable the Service Broker by disabling it
with the ALTER DATABASE command. The SET command is "DISABLE_BROKER".
You are building a .NET assembly that will access the registry of the local machine
for a factor used in a computer column. What permission set should you assign it?
EXTERNAL_ACCESS
SQL Server Interview Questions
http://marancollects.blogspot.com 22/35
The most restrictive permission set should always be used for .NET assemblies in
keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and
UNSAFE permission sets will allow registry access and UNSAFE permissions are not
required.
In SQL Server 2005, you want to monitor your transactional replication and
determine how long it takes for a transaction to move from the publisher to a
subscriber. What would you use to help you do this?
Tracer Tokens.
You can use tracer tokens in SQL Server 2005 replication to measure latencies.
These are small amounts of data written into your transaction logs.
How many credentials can one login be mapped to?
1
A credential can be mapped to many logins, but a login can only be mapped to one
credential.
You are looking to secure your publisher to prevent unauthorized logins from
accessing the data through replication. Where do you add the list of logins that have
access?
Add them to the Publication Access List in the publication database.
All access to publications is controlled with the Publication Access List (PAL), which is
similar to the Windows ACLs. A list is created for each publication that is set up.
What does the ERROR_PROCEDURE() function return?
The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH
construct and returns the name of the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used to access what?
Resources outside the SQL Server
A credential is used to access resources outside of SQL Server. It contains the
authentication information needed for accessing these resources.
Which of the following is an invalid file name for a database file?
None of the above
Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file
name extensions, but these extensions help you identify the different kinds of files
and their use.
SQL Server Interview Questions
http://marancollects.blogspot.com 23/35
What does @@options return?
The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection.
This is returned as integer and each bit in the integer represents an option.
You have setup forwarding for your SQL Server 2005 Service Broker queues to
another server. You edit a route to move a queue to a new server. What do you need
to do now to ensure you can recover the routes in the event of a disaster?
Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb
database, the routes will be captured.
You are looking to setup a large Notification Services application and you are
planning on a 40GB database. When installing Notification Services, the creation
fails. What is the likely problem?
Notification Services installation has a 10 minute timeout and the database creation
probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the
database takes longer to create, the installation of the instance fails. You need to
reduce the size of the database for installation and then increase it when you are
done.
You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM
on one of your servers. An ASP.NET application on the same server is feeling
memory pressure and you want to release some memory and reduce SQL Server to
3GB or RAM. How can you do this?
Change the max amount of memory allocated and shut down and restart SQL Server
to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server
is restarted.
You are troubleshooting a Service Broker application and find that messages are
remaining on the queue. You determine that there is no active queue monitor. What
should you do?
Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an
active queue monitor. If one is not on, then it needs to be activated. The ALTER
QUEUE statement is used to change this.
SQL Server Interview Questions
http://marancollects.blogspot.com 24/35
You are implementing replication across the Internet for a large bank that wishes to
move up to date pricing information to an analyst's laptop. They are concerned about
spoofing of their main site by criminals. What can you do to secure the replication
solution with SQL Server 2005?
Set the encryption level to verify the certificate is issued by a trusted authority.
Since you are asked to use replication, a replication solution is to use encryption for
the connection and verify the certificate being used is from a trusted authority.
Setting the encrpytion level to 2 will do this.
In SQL Server 2005, you are looking to implement full-text search. One of the tables
you are looking to index stores Mircrosoft Word documents in a varbinary(max)
column. Can you use Full-text search to index this column?
Yes
You can use full-text search for formatted data such as Word that contains text
stored in a varbinary column.
To recover an instance of Notification Services in 2005 on another server, what
information would you need?
A backup of the database holding Notification Services and the XSD and XSLT files as
well as the name and password for the service account.
To recover SSNS, you need the database backup as well as the operational files, XSD
and XSLT files, stored on the file system and the account information used for the
service account. Since SSNS usually works with data outside of the SQL Server, a
domain account is usually used to ensure proper permissions.
What type of connectivity does the readpipe/makepipe utility test?
Named Pipes
The readpipe and makepipe utility combination will test named pipe connectivity.
In SQL Server 2005, which of the following schema changes are supported for the
publication objects of a replicated database?
All of the above.
SQL Server 2005 replicated databases support the following schema changes for
objects:
* ALTER TABLE
* ALTER VIEW
* ALTER PROCEDURE
* ALTER FUNCTION
* ALTER TRIGGER (DML only)
SQL Server Interview Questions
http://marancollects.blogspot.com 25/35
You want to disable the receipt of messages in one of your databases by the Service
Broken. How should you do this?
ALTER DATABASE Sales SET DISABLE_BROKER
To disable the receipt of messages, you can disable the Service Broker by disabling it
with the ALTER DATABASE command. The SET command is "DISABLE_BROKER".
You are building a .NET assembly that will access the registry of the local machine
for a factor used in a computer column. What permission set should you assign it?
EXTERNAL_ACCESS
The most restrictive permission set should always be used for .NET assemblies in
keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and
UNSAFE permission sets will allow registry access and UNSAFE permissions are not
required.
What What does the term ACID refer to?
An acronym that describes concurrency support in a database
The answer is an acronym that describes concurrency support in a database. ACID is
an acronym for the following four terms: * Atomicity - Every operation within a
transaction is treated as a singular operation; either all of its data modifications are
performed, or none of them is performed.
* Consistency - Once the transaction is completed, the system must be left in a
consistent state.
* Isolation - It means that the operations within a transaction must be suitably
isolated from other transactions. In other words, no other transactions should see
data in the intermediate state, within the transaction, until it is finalized. This is done
by using locks.
* Durability - Once a transaction is competed, all changes must be persisted as
requested. The modifications should persist in total even in the event of a system
failure.
What does this return?
SELECT USER_NAME() -- Returns Andy
EXECUTE AS TechPreparation
GO
EXECUTE AS Steve
GO
REVERT
GO
SQL Server Interview Questions
http://marancollects.blogspot.com 26/35
SELECT USER_NAME()
TechPreparation
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?
png
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 two tables.
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.)
11-16
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?
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 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?
2
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
SQL Server Interview Questions
http://marancollects.blogspot.com 27/35
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 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?
Full
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?
Yes
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?
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 database user?
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.
SQL Server Interview Questions
http://marancollects.blogspot.com 28/35
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?
varchar(max)
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 the table.
Yesterday's QOD How are comments denoted in an XML document?
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 views?
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 problem?
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 fashion?
Like a queue, first in, first out.
SQL Server Interview Questions
http://marancollects.blogspot.com 29/35
Messages transferred in Service Broker work in a queue fashion with the first
message being sent being the first processed.
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?
db_accessadmin
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 logins.
What does this method in SQL Server 2005's SMO equate to in T-SQL?
db.CheckTables(RepairType.None)
DBCC CHECKDB WITH NO_INFOMSGS
This SMO method executes a DBCC CHECKDB with the NO_INFOMSGS option set.
In SQL Server 2005, can you include a user-defined role as a member of another
user-defined role?
Yes
Yes you can.
In SQL Server 2005, what does instance aware mean for a service?
The service is installed once for each instances on the server.
An instance aware service is one that is installed for each instance on the server. An
instance-unaware service is only installed once , no matter how many instances.
Integration Services is instance-unaware whereas SQL Agent is instance aware.
If you use the EXECUTE AS clause to impersonate a user at a database-scoped level.
Can you execute a query through a linked server if the impersonated user has the
rights to do so when normally logged in? No trust relationships exist between the
databases.
No
If you change your execution context to a user, which is scoped at a database level,
then any queries to linked servers or other databases will fail. There is a way around
this restriction if trust relationships are setup.
The nesting level for stored procedures in SQL Server 2005, but how many
databases can you access inside one stored procedure?
SQL Server Interview Questions
http://marancollects.blogspot.com 30/35
8
The footnote for nested stored procedures limits the number at 8.
Is it possible to build an index on a view?
Yes
Yes, an index can be put on a view but the first index must be a unique clustered
index.
What does REVERT do in SQL Server 2005?
Restores your previous execution context.
If you have changed your execution context with EXECUTE AS, the REVERT
statement will restore the last context prior to the EXECUTE AS.
What does SEND do in SQL Server 2005?
Sends a service broker message using a conversation.
SEND is used to send a message on an existing conversation in the Service Broker
architecture.
In SQL Server 2005, most data for your data types is stored in the pages for the
table. Binary objects, such as image or text, are stored in LOB or large Object data
pages. Where is varchar(max) data stored?
In LOB pages
Since the size restrictions for varchar(max) are the same as for text data, this data
is stored in LOB data pages.
What will occur with the following code in SQL Server 2005 if executed by Alice?
execute as user = 'Bob' with no revert
select user_name()
go
revert
select user_name()
go
You will receive as results:
bob
An error
The NO REVERT option with EXECUTE AS prevents the return of execution context to
SQL Server Interview Questions
http://marancollects.blogspot.com 31/35
the previous value. If you run the REVERT statement, you will receive the following
error:
You have to create some T-SQL that produces an order of players in a golf
tournament. Players who finish on the same number of strokes have to be given the
same finishing place denoting that they will have an equal prize amount. What T-SQL
function can achieve this?
RANK alongside the OVER function where the OVER function uses the players
number of strokes taken
The rank function will return the rank of the row in the result set. Ties will receive
the same rank. The OVER clause will separate out the ranking into partitions and use
those for calculating the proper placement of the golfers.
You want to disable the trigger dEmployee on the HumanResources.Employee table
in the SQL Server 2005 AdventureWorks sample database. Which of the following
statements will do the trick?
Answer
A and B
Explanation
Either of the commands in A and B will disable the trigger.
In SQL Server 2005, if you want the keys securing your data to expire over time,
what type of key encryption would you choose?
Answer
Certificates
Explanation
Certificates have an expiration data associated with them. This allows you to issue
them to users and force a reissue in order to maintain their access to data.
Which is larger in size in SQL Server 2005 if you are using symmetric keys?
SQL Server Interview Questions
http://marancollects.blogspot.com 32/35
Answer
Ciphertext
Explanation
Ciphertext is larger and can be calculated using the following formula:
Size = ( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK + 16)
where D is the data size in bytes, BLOCK is the block size (8 or 16, depending on
algorithm) and Size is the new size in bytes of the ciphertext.
What result would you expect from this statement?
Select * from dbo
.MyTable
All records from [dbo].[MyTable]
The answer is all records from [dbo].[MyTable]. Qualified names can include white
space before and after the dots. Restrictions apply only to the names themselves
that may need quoted identifiers with embedded white space or invalid characters.
You are using SQL Server 2005 and you wish to find a number of details about
stored procedures created. The details include date and time, the server that created
the stored procedure, the login name and the contents. How would you do this?
Create a DDL trigger on CREATE_PROCEDURE database event
The answer is Create a DDL trigger on CREATE_PROCEDURE database event You can
find the answer on page 448 of Beginning SQL Server 2005 for Developers: From
Novice to Professional
A .NET assembly running with UNSAFE permissions can do which of the following?
All of the above
CLR code running with UNSAFE permissions can access virtually any resource inside
or outside of SQL Server without restriction. This is one reason only a sysadmin can
create an assembly as UNSAFE.
What does the SQL Writer service do?
Handles Volume Shadow Service Copy functions.
The SQL Writer service was introduced to handle access to the data files in SQL
Server. It allows backup programs, like VSS, to function while SQL Server is still
running.
Which key provides the strongest encryption?
AES (256 bit)
SQL Server Interview Questions
http://marancollects.blogspot.com 33/35
The longer the key, the better the encryption, so choose longer keys for more
encryption. However there is a larger performance penalty for longer keys. DES is a
relatively old and weaker algorithm than AES.
You are delegating permissions on your SQL Server 2005 server to other
administrators. You have local, single server jobs on one server that you would like
to allow another administer to start, stop, and view the history for, but not delete
history. This administrator will own the jobs. Which role should you assign?
SQLAgentUserRole
SQL Server 2005 provides 3 fixed roles for the agent service that limit privileges for
administrators. The SQLAgentUserRole is designed for local jobs (not multiserver)
that allows the member to work with their owned jobs (edit, start, stop, view
history) without deleting the history of any job.
What level of permissions does a user need to create an assembly with UNSAFE
permissions?
sysadmin
Only a sysadmin can create assemblies that are specified as unsafe.
Which optional SQL Server component must you install to ensure that you can create
and execute maintenance plans?
SQL Server Integration Services
You must install Integration Services in order to properly create and execute
maintance plans since these plans are a subset of SSIS packages.
You have tightly secured access to your SQL Server 2005 server by placing it behind
a firewall and limited ports 1433 to those clients that need to directly access the SQL
Server. One of your developers complains about connecting to the Integration
Services esrvice on your server. What could be the problem if you know the service
is working properly?
The firewall needs to allow port 135 through for Integration Services
Integration Services requires access to both DCOM and port 135. You need to open
this port to allow the developer to communicate with SSIS.
In SQL Server 2005, which network protocols use endpoints?
All of the above
All communication points with SQL Server using network protocols use an endpoint.
SQL Server creates defaults for each protocol that you enable.
You have a SQL Server 2005 cluster and need to add anti-virus software as per your
corporate standards. What should you exclude from scans?
SQL Server Interview Questions
http://marancollects.blogspot.com 34/35
Log and data files for your databases as well as the Quorum drive and the backup
folders.
Anti-virus programs can exist on the same server as SQL Server without an issue if
you exclude certain items. The database data and log files, backup files and folders,
the quorum drive for clusters, temporary replication files, the SQL Server log files,
and Log Shipping folders/files. You do not need to exclude that SQL Server
executables and probably do not want to so that they are protected.
What port do you need to open on your server firewall to enable named pipes
connections?
Port 445
Named pipes communicates across TCP port 445.
When the network service built-in account connects to a network resource, what is
the context?
The computer account of the Windows installation.
The network service account connects to network resources as the computer account
for the Windows installation.
A certificate is like which type of key in SQL Server 2005 encryption?
An Asymmetric Key
A certificate is like an asymmetric key and can validate the identity of the issuer.
How many connections are available in SQL Server MSDE 2000?
Unlimited, no different from the other SQL Server 2000 editions.
The answer is actually an unlimited number of connections, though this in a practical
sense is limited by memory on the machine (The actual limitation is 32k
connecitons). Instead there is a governer that kicks in to limit things to 8 operations,
of which 3 are system operations in some cases. But these are operations, not
connections.
What will be the result of the last SELECT statement if you execute the T-SQL script
below as a sysadmin.
USE master
GO
IF DB_ID('test_schema') IS NOT NULL
DROP DATABASE test_schema
GO
CREATE DATABASE test_schema
GO
SQL Server Interview Questions
http://marancollects.blogspot.com 35/35
USE test_schema
GO
CREATE SCHEMA Schema1
CREATE TABLE Table1 (col1 int)
GO
CREATE SCHEMA Schema2
GO
CREATE TABLE Table1 (col1 int)
GO
SELECT SCHEMA_NAME(schema_id), name FROM sys.objects
WHERE name = 'Table1'
Schema1 Table1
dbo Table1
The correct answer is A. You can combine the CREATE SCHEMA with a CREATE
TABLE. You can even specify GRANTs or DENYs. Search Books online for CREATE
SCHEMA.
