Interview Questions And Answers (SET -4)
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.
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.
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.
tool is available for ad hoc reporting by non IT end users in Reporting Services
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.
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
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.
types of replication work with Oracle publishers?
Snapshot and transactional
Oracle publishing with SQL Server works as both snapshot and transactional
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.
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.
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)
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.
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
The snapshot still queries the original
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.
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
Web Services require what type of endpoint in SQL Server 2005?
Native XML Web Services in SQL Server 2005 require HTTP endpoints to communicate
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.
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.
SQL Server 2005 Full-Text Search, you set up a replacement set like this:
<sub>SQL Server 2005 </sub>
<sub>SQL Server 2K5 </sub>
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
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".
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?
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.
TRUSTWORTHY database property is by default?
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
are Notification Services event messages logged in SQL Server 2005?
In the Windows Application Log
Event messages are logged in the Windows Application log.