Set - 2

Question 1 :

What does a @@fetch_status of -2 mean in SQL Server 2005?

Answer :

The row being fetched is missing.
This means that the row that was being fetched from the cursor is missing.

Question 2 :

You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?

Answer :

Use Database Mirroring to fail over between the old reporting database and the new one.
The update process for a Scalable Shared database with minimal downtime involves putting out a new copy of the database, detaching the old database from each server, and then attaching the new database to each server.

Question 3 :

What is the cost threshhold for parallelism in SQL Server 2005?

Answer :

This is the number of seconds that a serialplan cannot exceed if it is to be used. A parallel plan is used if the estimate exceeds this value.
This is the threshold at which SQL Server determines whether a serial or parallel plan is to be used. When SQL Server calculates that a serial plan exceeds the threshold, it will elect to use a parallel plan instead.

Question 4 :

You have a Scalable Shared Database setup for reporting purposes on SQL2. You want to be able to keep a point in time view of the reporting database each month. What can you do?

Answer :

Make a new copy of the production database each month and then copy that to the SAN. Attach it as a new Scalable Shared Database each month to the reporting servers.
A Scalable Shared Database does not support database snapshots, so you would have to manually create a new database each month with the data view you need and add this as a new Scalable Shared Database to the SAN and each reporting server.

Question 5 :

You have an old database that needs to run in compatibility mode 65 on your SQL Server 2005 server. Which framework would you use to manage this database programmatically?

Answer :

SMO does not support compatibility modes 60 or 65, so you would need to use DMO instead.

Question 6 :

You have two Service Broker instances running. One is on SQL1 with the default collation and the other is on SQL2 setup for French collation. Which collation is used for Service Broker messages sent between the instances?

Answer :

Service Broker does not consider the collation, using byte-by-byte matching for names.
Neither collation is used. Service Broker operates in a collation independent method that removes collation information from the messages.

Question 7 :

What does the max full-text crawl range option do?

Answer :

Determines the number of partitions used in an index crawl.
This option helps optimize the full-text indexing process by specifying the number of partitions the SQL Server uses during index crawls.

Question 8 :

Which of the following is not an allocation unit in SQL Server 2005?

Answer :

The three types of allocation units are: IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. Each heap or index has IN_ROW_DATA which holds part of the data. LOB_DATA is used for large object data types and ROW_OVERFLOW_DATA is used for varible length data that causes a row to exceed the 8060 byte limit.

Question 9 :

Which of the following is the best use for a Scalable Shared Database in SQL Server 2005?

Answer :

A reporting database server
A scalable shared database is a feature that allows you to setup read-only database on a separate server for reporting purposes. This database provides an identicle view of your data from another server.

Question 10 :

You are loading 100 rows of data into a narrow table that is heavily used by your production inventory queries. It was recommended that you drop the indexes on the table before the load and then rebuild them after the load is complete. Is this something you would do?

Answer :

This does not make sense.
For such a small number of rows, it is unlikely that dropping the indexes will improve the performance of your load. If this were 100,000 rows, then it might make sense.

Question 11 :

How can SQL Server Agent Mail send messages in SQL Server 2005?

Answer :

SQL Mail through Extended MAPI or Database mail.
SQL Server Agent Mail can be configured to use Database Mail or Extended MAPI.

Question 12 :

What is the scale of measurement for the cost threshold for parallelism setting in SQL Server 2005?

Answer :

This value measures the number of seconds for a plan where the optimizer chooses between serial and parallel plans.

Question 13 :

Which of the following statements best describes the filter capabilities of Report Builder?

Answer :

Users can do equals, greater than, less than, etc, plus they can do logical AND, OR, NOT operations. Users can also group filters to allow more advanced filters.
While it looks a little different than you may be used to, the filter builder is reasonably rich, allowing most standard evaluation types and rich boolean comparisons. The only weak spot in the set is no support for LIKE, you have to make do with CONTAINS.

Question 14 :

True or false, Report Builder supports user defined run time parameters?

Answer :

Absolutely true. Users can define any portion of a filter to be a run time prompt, letting other users easily change the filter as needed. Not only is it easy to set up, Report Builder automatically populates a list of all possible choices based on the column being filtered.

Question 15 :

Using Report Builder that is bundled with Reporting Services 2005, which of the following would work as a way to add a derived field to a report?

Answer :

Add a field to the model in Report Builder using the built in formula/function support
While Report Builder cannot be used to build or maintain models, it does allow you to add a virtual field that exists only within that report - to the end user it looks like the model is being modified.

Question 16 :

If you absolutely need a report to look the same regardless of what OS or viewing software is being used, which of the following file formats would be the best choice?

Answer :

TIFF is the correct answer. By rendering as an image there are no concerns about different fonts, problems with page breaks, etc. PDF's are almost as good and more commonly used of course, with the advantage that document maps are translated to bookmarks that are usable - something not possible with a raw image.

Question 17 :

True or false, Report Builder offers direct export to a Microsoft Access database as one of its supported export options?

Answer :

False. Supported formats include Excel, CSV, XML, TIFF, MHTML, and PDF. XML or CSV could be easily imported into Access, but there is way to add the data directly from Report Builder

Question 18 :

When discussing image support in Report Builder, which of the following is the most accurate statement?

Answer :

Users can add one or more images, but they will appear in the header or footer of the report, they cannot be added to the detail row. Images that are stored as row data in the database can be rendered at the detail level.
Multiple images can be added, but they appear in the header or footer depending on where placed on the report. The only way to get an image at the detail level (row based) is to have it be part of the database and included in the model.

Question 19 :

Using Report Builder, which of the following is the best statement about the formatting options for Boolean columns?

Answer :

Booleans are formatted as True/False and there are no other built in options, but you could build an expression using IIF that would let you do other formatting
From the format dialog there are no extra formatting options for Booleans, Report Builder renders them as True/False. Writing an expression that you add to the model view is the easiest way to work around this limitation.

Question 20 :

What is the easiest way to capture the SQL statement for a Report Builder report you're troubleshooting?

Answer :

Run Profiler
Profiler will work as long as you have permissions to profile the server and is the the best solution because it requires no change to the Report Server itself. There is a way to log all report SQL to a log file, but that option was not listed here and is better used if you want to do analysis rather than troubleshooting.

Question 21 :

Clicking File, Save in Report Builder does which of the following?

Answer :

Saves the report to the report server
File|Save writes the report to the Report Server. Users have the option to also save the report to disk by using File|Save to File. Report Builder users cannot modify the model.

Question 22 :

Which of the following choices show the three report formats supported by Report Builder ?

Answer :

Table, Matrix, Chart
Report Builder can build a report formatted as a table, chart, or matrix (cross tab), but only ONE can be used in any given report.

Question 23 :

Using Report Builder, which of the following statements is correct about formatting numbers?

Answer :

Users can pick from a small number of predefined formats and they have the option to specify a custom format
There are give built in formats; general, currency, percentage, two place decimal, and exponent. Users can also define a custom format using a .Net format string.

Question 24 :

True or false, Report Builder supports using the LIKE function inside filters?

Answer :

There is no LIKE support, the next best thing is the CONTAINS function which works as if you specified both a leading and trailing wild card.

Question 25 :

Which RAID levels store parity information?

Answer :

Only RAID 5 (of those listed) contains parity information.

Question 26 :

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?

Answer :

Vertical partitioning
If you are moving some columns from one table to a new table, this is vertical partitioning.

Question 27 :

On which platforms can you use Instant File Initialization to improve database growth performance in SQL Server 2005?

Answer :

Windows 2003 and XP Pro
Both Windows 2003 Server and later as well as Windows XP Professional support Instant File Initialization.

Question 28 :

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?

Answer :

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.

Question 29 :

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?

Answer :

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.

Question 30 :

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?

Answer :

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.