SQL Server Questions and Answers

[Kalyan]

SQL Server Questions and Answers

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

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

In the directory stored in the system variable TEMP.

Profiler uses the location specified for the TEMP system variable.

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.

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.

source : intervieiwghost