Microsoft SQL Server Interview Questions And Answers (SET -3)

Microsoft SQL Server Interview Questions And Answers (SET -3)



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

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.

What is the result of the following query?
declare @a int
declare @b int

set @a = 5
set @b = 11

select @a = @a ^ @b , @b = @b ^ @a, @a = @a ^ @b
print '@a = '+convert(varchar,@a)
print '@b = '+convert(varchar,@b)

@a = 11 , @b = 5
Here is the explanation:

Step 1
@a has the value of 5 and is binairy 101
@b has the value of 11 and is binairy 1011

After “select @a = @a ^ @b , @b = @b ^ @a, @a = @a ^ @b”
the values are
@a will get the value from 101 ^ 1011 = 1110 (=14)
@b have stil the value of 1011 (=11)

Step 2
After “select @a = @a ^ @b , @b = @b ^ @a, @a = @a ^ @b”
The values are
@a will stay at the value 1110 (=14)
@b will get the value from 1011 ^ 1110 = 101 (=5)

Step 3
After “select @a = @a ^ @b , @b = @b ^ @a, @a = @a ^ @b”

@a will get the value from 1110 ^ 101 = 1011 (=11)
@b will stay at the value 101 (=5)

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.

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.