Set - 1

Question 1 :

What is SQL Server ?

Answer :

SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL.

Question 2 :

Error severity 13 indicates what?

Answer :

Transactional deadlock errors.
This level of error severity indicates a transaction deadlock error.

Question 3 :

In which order do you perform an upgrade to SQL Server 2005 for replicated databases?

Answer :

Distributor, Publisher, then Subscriber.
You always perform an upgrade in this order: distributor, publisher, subscriber.

Question 4 :

How many Service Packs will be released for SQL Server 2005 in 2007?

Answer :

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.

Question 5 :

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?

Answer :

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.

Question 6 :

What is the recommended way to send mail from SQLAgent in SQL Server 2005?

Answer :

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.

Question 7 :

When you create a new Message Type in the SQL Server 2005 Service Broker, what does the Authorization parameter signify?

Answer :

The owner of the message type.
This parameter determines the owner of the message type. This defaults to the current user.

Question 8 :

What the heck does ATN2 do?

Answer :

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.

Question 9 :

How does a differential backup know which extents have changed so that it can be very quickly run?

Answer :

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.

Question 10 :

If you run this, what does it return?
select applock_mode('public', 'SalesApp', 'Transaction')

Answer :

The type of lock being held by an application that requested it.
This command returns the lock mode held by an application that was requested with the sp_getapplock procedure.

insert mytable select ''
insert mytable select ' '
select * from mytable where mychar = ''
select * from mytable where mychar = ' '

Question 11 :

How many rows are returned by these two select statements?

Answer :

2 and 2
Each select statement actually returns 2 rows. You can use this script to check this:
create table mytable
( id int identity(1,1)
, mychar varchar(20)
insert mytable select null
insert mytable select ''
insert mytable select ' '
set ansi_nulls on
set ansi_null_dflt_on on
select * from mytable
where mychar is not null
select * from mytable
where mychar <> '' and mychar is not null
select * from mytable
where mychar <> ' ' and mychar is not null
select * from mytable where mychar = ''
select * from mytable where mychar = ' '
set ansi_null_dflt_on off
set ansi_nulls off
drop table mytable

Question 12 :

What does the Queue Reader Agent do in SQL Server 2005 replication?

Answer :

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.

Question 13 :

What are the three possible functions of the plus (+) operator in SQL Server 2005, the base installed T-SQL?

Answer :

Add, string concatenation, unary plus
The three functions are Add, String Concatenation, and Unary Plus.

Question 14 :

The Sort component in SQL Server 2005 Integration Services is what type of component?

Answer :

Blocking Transformation
The Sort component is a blocking transformation meaning that it needs all rows in order to perform its function.

Question 15 :

If you received a "Performance Critical" status in the SQL Server 2005 replication monitor, what does this mean?

Answer :

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.

Question 16 :

Which of the following modules within SQL Server 2005 cannot be signed with a digital signature?

Answer :

DDL triggers
DDL triggers cannot be signed, but all the other objects can.

Question 17 :

What does this return?

Answer :

declare @i int
select @i = -5
select +@i

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.

Question 18 :

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))
insert Mytable select 'Apple'
insert Mytable select 'ant'
insert Mytable select 'Ball'
select * from MyTable where Mychar like '[^a]%'

Answer :

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".

Question 19 :

Where does Profiler store its temporary data in SQL Server 2005?

Answer :

In the directory stored in the system variable TEMP.
Profiler uses the location specified for the TEMP system variable.

Question 20 :

What is the Service Broker Identifier ?

Answer :

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.

Question 21 :

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?

Answer :

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.

Question 22 :

How are modified extents tracked in SQL Server 2005 (which internal structures)?

Answer :

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).

Question 23 :

What does this return?

Answer :

select (1324 & 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.

Question 24 :

What does the Log Reader agent in SQL Server 2005 replication do?

Answer :

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.

Question 25 :

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?

Answer :

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.