Set - 5

Question 1 :

What does @@MAX_PRECISION return?

Answer :

The maximum precision for numeric and decimal data.
This function returns the maximum precision for numeric and decimal data as set on the server. The default for SQL Server 2005 is 38.

Question 2 :

Which of the following columns can be indexed with SQL Server 2005 Full-Text Search?

Answer :

char, varchar, nvarchar, and varbinary, text, ntext, and image
All character columns, char, varchar and nvarchar columns including max, text and ntext, and image columns are valid for full-text searching.

Question 3 :

When starting SQL Server 2005 from the command line, what does the -h switch do?

Answer :

This switch reserves memory space for Hot-Add memory metadata, available with AWE enabled.
This switch is used with 32-bit SQL Server and AWE to reserve memory to reserve memory space for Hot-Add memory metadata.

Question 4 :

Janice has two tables, Employees and Orders. She has been asked to provide a report of the number of orders processed by each employee in the month of June. If an employee didn't process any orders, the report should reflect the employee's name and a zero for the number of orders. Which of the queries is the best one for Janice to use to return the information she has been requested to provide?

Answer :

E.LastName + ', ' + E.FirstName AS [Employee Name]
, ISNULL(O.[# of Orders], 0) [# of Orders]
FROM dbo.Employees E
, COUNT(*) [# of Orders]
FROM dbo.Orders
WHERE OrderDate >= '20060601'
AND OrderDate < '20060701'

GROUP BY EmployeeID) O
ON E.EmployeeID = O.EmployeeID
ORDER BY [Employee Name]

While it would seem BETWEEN would save Janice a bit of typing, there is a problem with it. BETWEEN corresponds, based on these queries to the same as:

WHERE OrderDate >= '20060601' AND OrderDat e<= '20060701'
It's the latter one that causes the query to be incorrect as it would potentially include orders placed on July 1st at midnight (20060701 corresponds to July 1, 2006 at 00:00:00). While this would be unlikely in a small retail environment, it is entirely possible in larger operations, especially those which are international in scope. Therefore, the use of BETWEEN in this case is not appropriate.
With respect to the GROUP BY, it must appear in the subquery on the Orders table, as given in the answer. When it occurs after the LEFT JOIN, the rows where there is an employee but no order for the month will be lost. Since Janice must report on employees who had no sales, this is unacceptable.

Question 5 :

In SQL Server 2005, you want to monitor your transactional replication and determine how long it takes for a transaction to move from the publisher to a subscriber. What would you use to help you do this?

Answer :

Tracer Tokens.
You can use tracer tokens in SQL Server 2005 replication to measure latencies. These are small amounts of data written into your transaction logs.

Question 6 :

How many credentials can one login be mapped to?

Answer :

A credential can be mapped to many logins, but a login can only be mapped to one credential.

Question 7 :

You are looking to secure your publisher to prevent unauthorized logins from accessing the data through replication. Where do you add the list of logins that have access?

Answer :

Add them to the Publication Access List in the publication database.
All access to publications is controlled with the Publication Access List (PAL), which is similar to the Windows ACLs. A list is created for each publication that is set up.

Question 8 :

What does the ERROR_PROCEDURE() function return?

Answer :

The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH construct and returns the name of the procedure that caused an error to be thrown.

Question 9 :

Credentials in SQL Server 2005 are used to access what?

Answer :

Resources outside the SQL Server
A credential is used to access resources outside of SQL Server. It contains the authentication information needed for accessing these resources.

Question 10 :

Which of the following is an invalid file name for a database file?

Answer :

None of the above
Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use.

Question 11 :

What does @@options return?

Answer :

The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection. This is returned as integer and each bit in the integer represents an option.

Question 12 :

You have setup forwarding for your SQL Server 2005 Service Broker queues to another server. You edit a route to move a queue to a new server. What do you need to do now to ensure you can recover the routes in the event of a disaster?

Answer :

Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb database, the routes will be captured.

Question 13 :

You are looking to setup a large Notification Services application and you are planning on a 40GB database. When installing Notification Services, the creation fails. What is the likely problem?

Answer :

Notification Services installation has a 10 minute timeout and the database creation probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the database takes longer to create, the installation of the instance fails. You need to reduce the size of the database for installation and then increase it when you are done.

Question 14 :

You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM on one of your servers. An ASP.NET application on the same server is feeling memory pressure and you want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this?

Answer :

Change the max amount of memory allocated and shut down and restart SQL Server to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server is restarted.

Question 15 :

You are troubleshooting a Service Broker application and find that messages are remaining on the queue. You determine that there is no active queue monitor. What should you do?

Answer :

Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an active queue monitor. If one is not on, then it needs to be activated. The ALTER QUEUE statement is used to change this.

Question 16 :

You are implementing replication across the Internet for a large bank that wishes to move up to date pricing information to an analyst's laptop. They are concerned about spoofing of their main site by criminals. What can you do to secure the replication solution with SQL Server 2005?

Answer :

Set the encryption level to verify the certificate is issued by a trusted authority.
Since you are asked to use replication, a replication solution is to use encryption for the connection and verify the certificate being used is from a trusted authority. Setting the encrpytion level to 2 will do this.

Question 17 :

In SQL Server 2005, you are looking to implement full-text search. One of the tables you are looking to index stores Mircrosoft Word documents in a varbinary(max) column. Can you use Full-text search to index this column?

Answer :

You can use full-text search for formatted data such as Word that contains text stored in a varbinary column.

Question 18 :

To recover an instance of Notification Services in 2005 on another server, what information would you need?

Answer :

A backup of the database holding Notification Services and the XSD and XSLT files as well as the name and password for the service account.
To recover SSNS, you need the database backup as well as the operational files, XSD and XSLT files, stored on the file system and the account information used for the service account. Since SSNS usually works with data outside of the SQL Server, a domain account is usually used to ensure proper permissions.

Question 19 :

What type of connectivity does the readpipe/makepipe utility test?

Answer :

Named Pipes
The readpipe and makepipe utility combination will test named pipe connectivity.

Question 20 :

In SQL Server 2005, which of the following schema changes are supported for the publication objects of a replicated database?

Answer :

All of the above.
SQL Server 2005 replicated databases support the following schema changes for objects:

Question 21 :

You want to disable the receipt of messages in one of your databases by the Service Broken. How should you do this?

Answer :

To disable the receipt of messages, you can disable the Service Broker by disabling it with the ALTER DATABASE command. The SET command is "DISABLE_BROKER".

Question 22 :

You are building a .NET assembly that will access the registry of the local machine for a factor used in a computer column. What permission set should you assign it?

Answer :

The most restrictive permission set should always be used for .NET assemblies in keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and UNSAFE permission sets will allow registry access and UNSAFE permissions are not required.

Question 23 :

You are developing security policy for your SQL Servers and have all of the data entry clerks needing access to a series of tables. You create a Data Entry role and assign the proper permissions as well as add the users.
You then find out that Bob is a part of the HR group because of cross training and needs to use the same objects from the same application except for the Vacation table, to which he should not have access. No column permissions are assigned. What should you do?

Answer :

Create an new role and DENY permission to the Vacation table for this role. Add Bob to this role.
To effectively handle security, you want to minimize the administrative burden. Bob is a member of the Data Entry role, and because of cross training, you do not want to remove him from this role, but you do need to DENY permission to the Vacation table. The best way to do this is with another role specifically to DENY this permission.

Question 24 :

You are trying to track down issues with a SQL Server 2005 application using Profiler. The part of the application you are checking uses the EXECUTE AS statement to change the context of every user to the user Bob. How can you determine which user is executing statements in Profiler?

Answer :

Check the Sessionloginname data column.
The SESSIONLOGINNAME column, not shown by default, will contain the original login name of the user even if their context has changed in the application.

Question 25 :

Assuming the column name is correct, which of the following is a valid cast operation in SSIS _expression language?

Answer :

(DT_STR, 20, 1252)[MyColumn]
The correct answer is (DT_STR, 20, 1252)[MyColumn]. The cast uses parens and then the cast type specification with the _expression to be converted afterwards.