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

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



What does the DEALLOCATE statement do in SQL Server 2005?
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.

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

Setting the TRUSTWORTHY database property to ON allows you to execute the following from within the context of the database
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.

What tool is available for ad hoc reporting by non IT end users in Reporting Services 2005?
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.

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

Which types of replication work with Oracle publishers?
Snapshot and transactional
Oracle publishing with SQL Server works as both snapshot and transactional publication issues.

What does @@textsize return?
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.

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

Before a user can build reports using Report Builder in Reporting Services 2005, which one of the following steps must be done first?
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)

What does the CREATE SERVICE statement do in SQL Server 2005?
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.

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

In Reporting Services 2005, how is Report Builder typically deployed to end users?
One click deployment launched from a menu on the Report Manager home page
One click deployment is fast and easy, users click the menu on the Report Manager page. It is not a web application, but rather a .Net Winform application.

Native Web Services require what type of endpoint in SQL Server 2005?
HTTP endpoints
Native XML Web Services in SQL Server 2005 require HTTP endpoints to communicate with clients.

If you have a replication scenario with one publisher, one distributor and one subscriber and the transaction isolation level is not set to serializable, what happens when a transaction fails on the publisher?
It is still sent to the distributor and executed on the subscriber.
A transaction that fails in the publication database is still sent to the distributor and subscriber. YUou can trap the error, but you would need to trap it on the subscriber as well.

When creating a full-text index on a BLOB column (image, binary or varbinary) where a file is stored, what happens if an incorrect file prefix is stored in the type column? (For instance: if a word document is stored in the image or a varbinary(MAX) column and the value of the type column is '.pdf'.
The row is not full-text indexed and an error message is written to the SQL Server Error Log
The answer is the row is not full-text indexed and an error message is written to the SQL Server Error Log. No columns in the row are full-text indexed and an error message is written to the SQL Server Error Log.

In SQL Server 2005 Full-Text Search, you set up a replacement set like this:

SS2K5
_{SQL Server 2005
SQL Server 2K5}

A search for "SS2K5" doesn't return results with fields that you know contain "SS2K5". What is wrong?

The replacement set does not return results with the search terms, only the specified replacements.

In the SQL Server 2005 thesaurus XML configuration file, what is the expansion set?
Expansion sets are synonyms for the search term and returned as results if they appear along with the search term.
The expansion set is the group of values that are synonyms and can be substituted for the search term. For example, an expansion set can be "SS2K5", "SQL Server 2005", "SQL2K5". In this case, fields with any of these 3 values would be returned as a result for searches on "SQL Server 2005".

You are initiating a new replication subscription for one of the remote offices, but don't want to transfer it across the network. The snapshot files are 725MB, too large for your CD burner and there is no tape drive at the remote office. What feature of SQL Server 2005 would help you get the snapshot transferred?
Compressed Snapshots
In SQL Server 2005, you can specify a snapshot to be compressed and it will be compressed using the CAB format. If it compresses enough, you should be able to burn it onto a CD.

The TRUSTWORTHY database property is by default?
Off
The correct answer is off – The TRUSTWORTHY database property indicates whether the installed instance of SQL Server trusts the database such that it can reach out and perform actions outside the database. By default it is OFF such that the database to reduce certain threats that can result from attaching a database that contains potentially harmful code

Where are Notification Services event messages logged in SQL Server 2005?
In the Windows Application Log
Event messages are logged in the Windows Application log.