In SQL Server 2005, can you include a user-defined
role as a member of another user-defined role?
Yes you can.
In SQL Server 2005, what does instance aware mean for a
The service is installed once for each instances on the
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.
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
The nesting level for stored procedures in SQL Server
2005, but how many databases can you access inside one
The footnote for nested stored procedures limits the
number at 8.
Is it possible to build an index on a view ?
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
What will occur with the following code in SQL Server
2005 if executed by Alice?
execute as user = 'Bob' with no revert
You will receive as results:
The NO REVERT option with EXECUTE AS prevents the return
of execution context to the previous value. If you run
the REVERT statement, you will receive the following
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
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?
A and B
Either of the commands in A and B will disable the
In SQL Server 2005, if you want the keys securing your
data to expire over time, what type of key encryption
would you choose?
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?
Ciphertext is larger and can be calculated using the
Size = ( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK +
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
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
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
Which key provides the strongest encryption?
AES (256 bit)
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?
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.
Page Numbers : 1