Set - 3

Question 1 :

Which utility is used to administer SQL Server 2005 Notification Services instances?

Answer :

The nscontrol application can be used with various parameters to administer a SQL Server 2005 Notification Services instance.

Question 2 :

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?

Answer :

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.

Question 3 :

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)

Answer :

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

Question 4 :

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?

Answer :

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.

Question 5 :

Can a particular event in SQL Server 2005, such as the CREATE USER command, have more than one DDL trigger assigned to it?

Answer :

An event can have multiple triggers assigned to it.

Question 6 :

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?

Answer :

You cannot re-assocaite the application because when you add the application, SSNS recreates the objects. If they already exist, the create fails.

Question 7 :

How many users can be added to an application role in SQL Server 2005?

Answer :

This is a trick questions. No users are added to application roles. Application roles are invoked by a user.

Question 8 :

What algorithm is used to encrypt the Database Master Key when it is created?

Answer :

Triple DES
When you create a Database Master Key, it is encrypted using the password you supply with the Triple DES algorithm.

Question 9 :

Which protocols support Kerberos authentication on SQL Server 2005?

Answer :

Only those clients connecting with TCP/IP can use Kerberos authentication.

Question 10 :

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?

Answer :

Windows 2003 Server
You can only enforce password policy on the Windows 2003 Server platform and newer.

Question 11 :

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?

Answer :

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.

Question 12 :

Where can you view the list of server-scoped DDL triggers?

Answer :

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.

Question 13 :

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?

Answer :

You cannot back up a database snapshot.
A database snapshot cannot be backed up or restored.

Question 14 :

When you install SQL Server 2005 and create a new database, is a Database Master Key created?

Answer :

A database master key is not created when a database is created. It must be created by an administrator.

Question 15 :

What does the DEALLOCATE statement do in SQL Server 2005?

Answer :

Remove a reference to a cursor.
This statement is used to remove cursor references. If this is the last reference being removed, the cursor's resources are released.

Question 16 :

Report Builder has no built in support for source control. Which of the following choices could be implemented if you decided that those reports needed to be under source control?

Answer :

Have users save the RDL files locally and check into source control using a source control utility, or write code to script them out each night and check them into source control
Users can do faux source control by just saving copies of their RDL files locally before they make a change. Beyond that, you either teach them how to use a source control utility or you write a utility you can run on a schedule.

Question 17 :

Setting the TRUSTWORTHY database property to ON allows you to execute the following from within the context of the database ?

Answer :

Objects from unsigned CLR assemblies with EXTERNAL_ACCESS or UNSAFE permissions
In order to execute objects from CLR assemblies with EXTERNAL_ACCESS or UNSAFE permissions, the assemby must either be signed with a key pair or the database hosting the assembly must have the TRUSTWORTHY database property set to ON. Executing the other answers is essentially controlled by permissions. Setting the TRUSTWORTHY database property to ON also allows you to use the EXECUTE AS clause to allow impersonation outside the scope of the database within the instance but this was not one of the answers.

Question 18 :

What tool is available for ad hoc reporting by non IT end users in Reporting Services 2005?

Answer :

Report Builder
Report Builder is bundled as part of Reporting Services 2005 and is targeted at end users needing to build ad hoc reports. BI Design Studio could be used, but typically requires more time and experience to use than the average end user will be willing to invest.

Question 19 :

You have just upgraded your SQL Server 2000 instance on Windows 2000 to SQL Server 2005. You are looking to begin implementing encryption capabilities and need to choose an algorithm for your data. Which of the following is not available to you?

Answer :

128-bit AES
While all of these are valid encryption algorithms on SQL Server 2005, the AES algorithms (128-bit, 192-bit, and 256-bit) are not supported on Windows XP or Windows 2000. To use these, you would need to upgrde to Windows 2003.

Question 20 :

Which types of replication work with Oracle publishers?

Answer :

Snapshot and transactional
Oracle publishing with SQL Server works as both snapshot and transactional publication issues.

Question 21 :

What does @@textsize return?

Answer :

The current value of the TEXTSIZE option for the SET command.
This returns the current value of the TEXTSIZE option that can be changed with the SET command. The default is 4096 bytes.

Question 22 :

In configuring the thesaurus configuration file for SQL Server 2005 Full-Text Search, you set the diacritics_sensitive value to 1. What does this mean?

Answer :

It means the sets in this file are accent sensitive.
The diacritics_sensitive determines if the terms in the expansion and replacement sets are accent sensitive. A value of 0 means accent insensitive and a value of 1 means accent sensitive.

Question 23 :

Before a user can build reports using Report Builder in Reporting Services 2005, which one of the following steps must be done first?

Answer :

Build and deploy a model
Report Builder requires a model and that model cannot be defined using Report Builder. The advantage of using models is that users do not need to understand SQL statements to build a report (but they will still benefit from a basic understanding of the relationships between various bits of data)

Question 24 :

What does the CREATE SERVICE statement do in SQL Server 2005?

Answer :

This statement is used to setup new tasks for Service Broker queues.
This statement is ue to create a new Service Broker service that is a set of tasks that are run on messages.

Question 25 :

You have a SQL Server 2005 sales database experiencing performance problems because of heavy I/O activity. You decide to create a snapshot of this database every hour, stored on a separate physical disk array and use that for reporting queries. However the queries still seem slow immediately after snapshot creation. Why?

Answer :

The snapshot still queries the original database.
The snapshot of the production database will only reduce I/O for changed pages. Any data that has not been changed will be read from the source database, which is in this case is experiencing heavy I/O itself.