(Continued from previous part...)

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

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.
Explanation
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 = ' '

How many rows are returned by these two select statements?

Answer
2 and 2

Explanation
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)
)
go
insert mytable select null
insert mytable select ''
insert mytable select ' '
go
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
go
drop table mytable

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.
Explanation
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?

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

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

Answer
Blocking Transformation

Explanation
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?

Answer
The latency between transaction commits at the publisher and subscriber exceeds the warning level.

Explanation
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?

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

What does this return?
declare @i int
select @i = -5
select +@i

Answer
-5

Explanation
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]%'

Answer
Ball

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

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

Answer
In the directory stored in the system variable TEMP.

Explanation
Profiler uses the location specified for the TEMP system variable.

(Continued on next part...)