Two Minute Stumpers - V4

In association with

Two Minute SQL Server Stumpers Test your SQL Server 2005 knowledge!

Vol. 4

Shelving: Database/SQL Server

$24.99 USA

Two Minute SQL Server Stumpers Vol. 4 Brought to you by the staff at SQLServerCentral.com And Red Gate Software

Thanks to the following for contributing questions: Jamie Thomson Jon Reade

Central Publishing Group P.O. Box 9510 Fleming Island, FL 32006 Copyright Notice Copyright 2006 by The Central Publishing Group. All rights reserved. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced under the Copyright Act of 1976. No part of this publication may be reproduced in any form or by any means or by a database retrieval system without the prior written consent of The Central Publishing Group. The publication is intended for the audience of the purchaser of the book. This publication cannot be reproduced for the use of any other person other than the purchaser. Authors of the material contained in this book retain copyright to their respective works. Disclaimer The Central Publishing Group and the individual authors of the questions and answers are not liable for any problems or issues resulting from using this information. It is intended strictly as a learning aid and is not a final source of reference. Trademarks Microsoft, SQL Server, Windows, .NET, and Visual Basic are registered trademarks of Microsoft Corporation.

Forward Why Two Minute Questions? When I was a kid, I used to order these Scholastic mysteries from the school. They were these little, thin paperbacks, like 50-60 or so pages, much like what you’re holding now, that had a short mystery written on a page. Usually they consisted of some story and then a question. How did someone get killed? Where did the murder weapon go? Something like that. You then turned the page to find out the answer after thinking about it for a minute. We now bring you Volume 4, and once again, that’s what we’re trying to build here, but instead of some mystery, it’s a quiz type format of SQL Server questions and answers, culled from the annals of our very popular Question Of The Day on the SQLServerCentral.com website. These are a collection of questions from the past that we put together to help you study for an exam, learn a bit more about SQL Server, pass the time, etc. But they’re mostly collected for… The INTERVIEW. You’re looking for a new job, you’ve posted your resume, worked on cover letters, and finally landed an interview. Now you want to be sure that you look your best; that you can answer what’s thrown at you. I can’t promise that anyone will ask you any of these questions, but you never know. Maybe some managers that are interviewing will grab a copy of the book and start asking questions out of it. But it will help you prepare, give you some hands on experience, challenge you in a variety of ways about the different aspects of SQL Server. Some of the questions are arcane, some very common,

but you’ll learn something and the wide range of questions will help you get your mind agile and ready for some quick thinking. This version is a compilation of SQL Server 2005 questions, to bring you up to date on the latest version of SQL Server. So read on, in order, randomly, just start going through them, but do yourself a favor and think about each before turning the page. Challenge yourself and see how well you do. Thanks for your support and be sure to visit us online. Steve Jones SQLServerCentral.com

Question 1 - Administration When using Multiple Active Result Sets (MARS) and submitting multiple requests across a single connection, when does the server stop processing one request and move to the next? Choose one of the answers below: 1. At well defined points, usually the end of a statement. 2. After 200ms by default, but this can be changed. 3. It is interrupt driven by the connection. 4. After every 50ms or 1000 rows have been processed.

Answer: 1. At well defined points, usually the end of a statement. Only at well defined points, usually the end of a statement, will processing switch. Also, the processing switches when returning results, only if the send buffer on the server fills and is waiting on the client before transmitting more data. Ref: Multiple Active Result Sets (MARS) in SQL Server 2005 http://msdn.microsoft.com/en-us/dnsql90/html/MARSinSQL05.asp

Question 2 - Disaster Recovery You have a SQL Server 2005 database that uses Full Text search. Your new junior DBA accidently drops the database. What do you need to do for recovery? Choose one of the answers below: 1. Restore the database, then re-run the full text indexing process. 2. Restore the database. 3. Restore the database, drop and add back the full text indexes. 4. Restore the full text indexes from backup, then restore the database.

Answer: 2. Restore the database. In SQL Server 2005, the full text catalogs are backed up along with the databases, so a simple restore would bring back the catalog. Ref: SQL Server 2005 Full-Text Search: Internals and Enhancements http://msdn.microsoft.com/en-us/dnsql90/html/sql2005ftsearch.asp

Question 3 - Replication You are having trouble with all of the command prompt utilities on your SQL Server 2000 server. There must be an environmental variable or something set improperly as osql, itwiz, isql, makepipe and more are not working. You decide that you will just use Enterprise Manager to setup snapshot replication and run your pre and post T-SQL batches instead of scripting everything. What will be the problem? Choose one of the answers below: 1. Enterprise Manager doesn't allow running pre and post scripts. You will need to run these in QA manually before and after working on the snapshot replication. 2. Pre and post scripts are affected by a bug in Enterprise Manager and will never complete. 3. Pre and post scripts depend on osql to run and you will not be able to run them because of that. 4. The snapshot process requires isql and therefore will not work.

Answer: 3. Pre and post scripts depend on osql to run and you will not be able to run them because of that. Pre and post scripts in snapshot replication are run using osql and therefore will not execute on this server. Ref: Executing Scripts Before and After the Snapshot is Applied http://msdn.microsoft.com/library/enus/replsql/replimpl_688e.asp?frame=true

Question 4 - SQL Server 2005 What does SMO mean in SQL Server 2005? Choose one of the answers below: 1. Structured management objects 2. SQL Server Management Objects 3. Simple maintenance of objects 4. Structured manipulation objects

Answer: 2. SQL Server Management objects SQL Server Management Objects (SMO) are a new set of programming objects that expose the functionality of SQL Server database and replication management. SQL SMO is implemented as a .NET assembly and takes advantage of the CLR. SMO may be used to automate repetitive or commonly performed SQL Server administrative tasks, such as programmatically retrieving configuration settings, creating new databases, applying Transact-SQL scripts, creating SQL Server Agent jobs, and scheduling backups Ref: - http://msdn2.microsoft.com/en-us/library/ms162169.aspx

Question 5 - SQL Server 2005 What is database mirroring? Choose one of the answers below: 1. The new name for replication in SQL Server 2005. 2. A programming technique involving submitting the same update, insert, or delete statement to two separate servers. 3. This is where every update to a database is sent to another copy of this database on another server. 4. This is a one click technique for making a copy of a database on the same or different server with a new name.

Answer: 3. This is where every update to a database is sent to another copy of this database on another server. Database mirroring involves immediately reproducing every update to a database onto a separate full copy of the database. These two databases reside on different instances of the SQL Server 2005 database engine. Ref: Database Mirroring http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.msp x

Question 6 - SQL Server 2005 If you setup database mirroring, do you need to back up the mirror database? (as opposed to the principal database) Choose one of the answers below: 1. Yes 2. No 3. Only when a full backup occurs on the principal database.

Answer: 2. No You do not need to do this. Backing up the mirror database is not supported in SQL Server 2005. Ref: Database Mirroring - http://msdn2.microsoft.com/enus/library/ms189053.aspx

Question 7 - SQL Server 2005 What is the role of a witness in database mirroring? Choose one of the answers below: 1. The witness provides an independent copy of all transactions sent to the mirror database. 2. The witness is a third computer that can enable failover if the principal fails. 3. The witness is the licensing server for database mirroring. 4. The witness is another name for the mirror server.

Answer: 2. The witness is a third computer that can enable failover if the principal fails. The witness is an optional, additional instance of SQL Server 2005, residing on a 3rd computer. It monitors the status of the principal and mirror servers and can enable automatic failover if the principal fails. The mirror and witness must agree before a failover occurs. Ref: Database Mirroring http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.msp x

Question 8 - SQL Server 2005 SQL Server 2005 includes a new setting with the SET SHOWPLAN option to give you more options in analyzing your query plans. What is it? Choose one of the answers below: 1. SET SHOWPLAN_GIF - outputs an image of the showplan 2. SET SHOWPLAN_TREE - Shows a textual tree of the execution path 3. SET SHOWPLAN_PERF - Orders the execution plan by the item that uses the most resources to the item that uses the least resources to enable troubleshooting. 4. SET SHOWPLAN_XML - outputs an XML document of the execution plan.

Answer: 4. SET SHOWPLAN_XML - outputs an XML document of the execution plan. In SQL Server 2005, you can execute SET SHOWPLAN XML and execute a query with results to grid, the results set will contain the XML showplan as an XML document returned in a one-row, onecolumn table. You can then click on the internal link in the content of the cell to review the XML showplan within the Management Studio XML editor. The output is the same as when sending the results to text, but opening from the grid results cell invokes the XML editor. Ref: - http://msdn2.microsoft.com/en-us/library/ms187757.aspx

Question 9 - SQL Server 2005 What does MARS stand for in SQL Server 2005? Choose one of the answers below: 1. Multiple Asynchronous Read System 2. Multiple Active Result Sets 3. Merge Archival Retrieval System 4. Multiple Active Response System

Answer: 2. Multiple Active Result Sets MARS is an acronym for Multiple Active Result Sets, a new construct in SQL Server 2005 that allows a single connection to simultaneously have multiple requests and results moving across the same connection. Prior to this, only a single request at a time could exist on a connection. Ref: MARS in SQL Server 2005 (http://msdn2.microsoft.com/enus/library/ms345109.aspx).

Question 10 - SQL Server 2005 In SQL Server 2005, is the schema the same as the owner in SQL Server 2000? Choose one of the answers below: 1. No 2. Yes

Answer: 1. No The schema is not the same as the owner in SQL Server 2005. While they are similar in that they both are containers of objects, the owner could not be dropped in SQL Server 2000, but it acted as the container for objects. In SQL Server 2005, the owner can be associated with a schema, but the schema is the container for objects. The owner is separate and can be dropped without affecting the schema and it's objects. Ref: User-Schema Separation - http://msdn2.microsoft.com/enus/library/ms190387(SQL.90).aspx

Question 11 - SQL Server 2005 Which of the following error functions is not valid in SQL Server 2005? Choose one of the answers below: 1. error_level() 2. error_line() 3. error_state() 4. error_number()

Answer: 1. error_level() The following functions are valid in SQL Server 2005: error_number() error_message() error_severity() error_state() error_line() error_procedure() The error_level() function is not valid. Ref: error_number() http://msdn2.microsoft.com/enus/library/ms175069(en-US,SQL.90).aspx error_message() http://msdn2.microsoft.com/enus/library/ms190358(en-US,SQL.90).aspx error_severity() http://msdn2.microsoft.com/enus/library/ms178567(en-US,SQL.90).aspx error_state() http://msdn2.microsoft.com/en-us/library/ms180031(enUS,SQL.90).aspx error_line() http://msdn2.microsoft.com/en-us/library/ms178600.aspx error_procedure() http://msdn2.microsoft.com/enus/library/ms188398(en-US,SQL.90).aspx

Question 12 - SQL Server 2005 In SQL Server 2005, the management objects support something called partial instantiation. What is this? Choose one of the answers below: 1. Partial Instantiation allows you to instantiate a partial object that contains only properties with none of the methods loaded. 2. Partial instantiation allows you to instantiate an object and it's child collections are not loaded until referenced. 3. Partial instantiation allows you to instantiate a child object without instantiating the parent object. 4. Partial instantiation is a joke. It does not exist in SQL Server 2005.

Answer: 2. Partial instantiation allows you to instantiate an object and it's child collections are not loaded until referenced. There are 3 levels of instantiation that you can set. Uninstantiated, nothing loaded, or full instantiated, everything loaded (like DMO) are the most common levels that most programmers are familiar with. There's also partial instantiation, which doesn't load objects that are not directly referenced. Once the object is referenced, then it is fully instantiated. Ref: SMO Overview http://msdn2.microsoft.com/zhcn/library/ms162557.aspx

Question 13 - SQL Server 2005 What is the maximum number of CPUs SQL Server 2005 Enterprise Edition will support? Submitted by Jon Reade Choose one of the answers below: 1. 4 2. 8 3. 16 4. 32 5. No Limit

Answer: 5. No Limit 5. There is no limit to how many CPUs SQL Server 2005 Enterprise Edition will support. Ref: SQL Server 2005 Features http://www.microsoft.com/sql/prodinfo/features/comparefeatures.mspx. http://www.sqlservercentral.com/columnists/bknight/comparisonofsqls erver2005editions.asp - Comparison of SQL Server 2005 Editions By Brian Knight, SQL Server Central, 28th February 2005.

Question 14 - SQL Server 2005 You are working with SQL Server 2005 and you wish to call an external program on your server from within the SQL Server. You receive this message: Msg 15501, Level 16, State 1, Procedure xp_cmdshell, Line 1 This module has been marked OFF. Turn on 'xp_cmdshell' in order to be able to access the module.

What can you do to fix it? Choose one of the answers below: 1. Run this: EXECUTE sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXECUTE sp_configure 'xp_cmdshell', '1' GO RECONFIGURE WITH OVERRIDE GO

2. In the SQLWorkbench, choose Server Properties, then the Security tab and deselect the checkbox marked "xp_cmdshell disabled". 3. You need to rerun SQL Server Setup and select the Advanced Options. Check the xp_cmdshell install to install this DLL into the server. 4. Run this: SET XP_CMDSHELL ON GO GRANT EXECUTE ON XP_CMDSHELL TO ADMINISTRATORS

Answer: 1. Run this: EXECUTE sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXECUTE sp_configure 'xp_cmdshell', '1' GO RECONFIGURE WITH OVERRIDE GO

By default the xp_cmdshell extended stored procedure is marked as off meaning it cannot be executed. One of the advanved configuration options can be set to 1 to enable this functionality. You use: EXECUTE sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXECUTE sp_configure 'xp_cmdshell', '1' GO RECONFIGURE WITH OVERRIDE GO

Ref: xp_cmdshell Option - http://msdn2.microsoft.com/enus/library/ms190693.aspx

Question 15 - SQL Server 2005 Database Mirroring is supported by which of the following editions of SQL Server 2005? Submitted by Jon Reade Choose one of the answers below: 1. Enterprise only 2. Enterprise and Standard 3. Enterprise, Standard and Workgroup 4. Enterprise, Standard and Developer 5. All editions except Express

Answer: 4. Enterprise, Standard and Developer SQL Server 2005 Enterprise, Standard and Developer Edition all support database mirroring. Note that only up to 2 CPUs per server are supported wih the Standard Edition. Ref: SQL Server 2005 Features http://www.microsoft.com/sql/2005/productinfo/sql2005features.asp. http://www.sqlservercentral.com/columnists/bknight/comparisonofsqls erver2005editions.asp - Comparison of SQL Server 2005 Editions By Brian Knight, SQL Server Central, 28th February 2005. Ref: SQL Server 2005 Features Comparison http://www.microsoft.com/sql/prodinfo/features/comparefeatures.mspx

Question 16 - SQL Server 2005 What is the maximum amount of RAM supported by SQL Server 2005 Standard Edition? Submitted by Jon Reade. Choose one of the answers below: 1. 3GB 2. 2GB 3. 8GB 4. 4GB 5. No Limit

Answer: 5. No Limit There is no limit on how much RAM the Standard Edition of SQL Server 2005 can utilize. The limitation is the support by the host operating system. Ref: SQL Server 2005 Features http://www.microsoft.com/sql/2005/productinfo/sql2005features.asp. http://www.sqlservercentral.com/columnists/bknight/comparisonofsqls erver2005editions.asp - Comparison of SQL Server 2005 Editions By Brian Knight, SQL Server Central, 28th February 2005.

Question 17 - SQL Server 2005 The SQL Server 2005 Service Broker implements what type of service? Choose one of the answers below: 1. Multi-server transactions, similar to the DTC, it can handle 2 or more servers in a single transaction. 2. Advanced scheduling of jobs across many servers. 3. Asynchronous messaging using queues. 4. Management and distribution of Integration Services Packages across all SSIS servers.

Answer: 3. Asynchronous messaging using queues. The Service Broker implements an asynchronous messaging system using queues. Ref: Introduction to Service Broker Programming http://msdn2.microsoft.com/en-us/library/ms345108.aspx

Question 18 - SQL Server 2005 In SQL Server 2005, the Free Text Searching has been substantially enhanced. What does the FREETEXT ranking algorithm do? Choose one of the answers below: 1. It ranks the matching of the search term against the number of characters from the beginning of the data field. The closer that a particular term is to the beginning of the field, the higher it's rank. 2. Each term in the query is ranked, and the values are summed. Freetext queries will add words to the query via inflectional generation (stemmed forms of the original query terms); these words are treated as separate terms with no special weighting or relationship with the words from which they were generated. Synonyms generated from the Thesaurus feature are treated as separate, equally weighted terms. 3. It ranks the search terms according to the Unicode-8 values that make up the term and compares those to the data fields. The closer the matches, including synonyms, the higher the rank. 4. It ranks the search terms according to the current collation of the table and the current sort order. The first term is weighted 3x the value of the second term, which is weighted 2x the value of subsequent terms to better match the most likely occurence values in the data field.

Answer: 2. Each term in the query is ranked, and the values are summed. Freetext queries will add words to the query via inflectional generation (stemmed forms of the original query terms); these words are treated as separate terms with no special weighting or relationship with the words from which they were generated. Synonyms generated from the Thesaurus feature are treated as separate, equally weighted terms. Each term in the query is ranked, and the values are summed. Freetext queries will add words to the query via inflectional generation (stemmed forms of the original query terms); these words are treated as separate terms with no special weighting or relationship with the words from which they were generated. Synonyms generated from the Thesaurus feature are treated as separate, equally weighted terms. Ref: SQL Server 2005 Full-Text Search: Internals and Enhancements http://msdn2.microsoft.com/en-us/library/ms345119.aspx

Question 19 - SQL Server 2005 The SQL Server Full Text Search Engine has been substantially enhanced over SQL Server 2000. What is one of the major architectural changes? Choose one of the answers below: 1. Each instance receives its own installation of the MSFTESQL service. 2. The SQL Turbo engine from Imceda was licensed to power the feature. 3. Each instance receives its own installation of the MSSearch service. 4. The Full Text Search engine is now powered by Google.

Answer: 1. Each instance receives its own installation of the MSFTESQL service. One major change in SQL Server 2005 is that the full text search service has been separated from the Operating System search service and each instance receives its own service, each one based on MSFTESQL. Ref: SQL Server 2005 Full-Text Search: Internals and Enhancements http://msdn2.microsoft.com/en-us/library/ms345119

Question 20 - SQL Server 2005 What does the MSFTEFD service do in SQL Server 2005? Choose one of the answers below: 1. This is the SQL Server 2005 Full Text Search Engine service. 2. This is the SQL Server 2005 service that loads and manages word breakers, stemmers, indexers, and other third party components for Full Text Search. 3. This is the SQL Server 2005 service that manages the full time Engine Data Protection. 4. This is the Profiler service that runs full time collecting all event data.

Answer: 2. This is the SQL Server 2005 service that loads and manages word breakers, stemmers, indexers, and other third party components for Full Text Search. MSFTEFD is the full-text filter daemon safely loads and drives thirdparty extensible components used for index and query, such as word breakers, stemmers, and filters, without compromising the integrity of the full-text engine service itself. Ref: SQL Server 2005 Full-Text Search: Internals and Enhancements http://msdn2.microsoft.com/en-us/library/ms142541.aspx

Question 21 - SQL Server 2005 When submitting multiple requests across a single connection using MARS, how many threads are used to process the requests? Choose one of the answers below: 1. One 2. One per request 3. All available threads in the worker pool, no more than 1 per thread. 4. All available threads needed in the worker pool to take advantage of parallelism.

Answer: 1. One All the requests across a single connection are limited to one spid and in most cases, one thread. A particular batch could be executed using parallelism, but two different batches will not run in parallel. They may switch execution at well defined points, but in general will run one at a time. Ref: http://msdn2.microsoft.com/en-us/library/ms345109.

Question 22 - SQL Server 2005 In SQL Server 2005 execution plans, what operation is equivalent to a SQL Server 7.0/2000 "Bookmark Lookup" on a heap? Submitted by Adam Machanic Choose one of the answers below: 1. Data Key Lookup 2. RID Lookup 3. Heap Lookup 4. Non-Clustered Transform Lookup

Answer: 2. RID Lookup RID Lookup. In SQL Server 2005, the "Bookmark Lookup" operation no longer appears in execution plans, having been replaced by the "RID Lookup", for heaps, or a seek into the clustered index in the case of non-heap tables. These seeks appear in conjunction with a Nested Loops/Inner Join operation, and represent the same activity the Bookmark Lookup operation used to, but give a clearer picture of what's actually happening. Reference: http://msdn2.microsoft.com/enus/library/ms190696.aspx

Question 23 - SQL Server 2005 In SQL Server 2005, if you create a database snapshot, how do you recover your production database from it? Choose one of the answers below: 1. Issue a RESTORE command using the FROM DATABASE_SNAPSHOT parameter. 2. Issue a ROLLBACK DATABASE with the USING DATABASE_SNAPSHOT comand. 3. You cannot recover from a database snapshot 4. Issue a normal restore using the database snapshot mdf file as the filename.

Answer: 1. Issue a RESTORE command using the FROM DATABASE_SNAPSHOT parameter. A database snapshot is a point in time view of a database that is live and available for querying. It can be used to restore the source database to that point in time using the restore command. If you had created a snapshot of Northwind named Northwind_SS, then you would run: RESTORE DATABASE Northwind FROM DATABASE_SNAPSHOT=Northwind_SS

Ref: http://msdn2.microsoft.com/en-us/library/ms189281.aspx http://www.simple-talk.com/sql/database-administration/sql-server2005-snapshots/

Question 24 - SQL Server 2005 In SQL Server 2005, this code creates how many partitions for data? create partition function My_Partitions( datetime) as RANGE RIGHT FOR VALUES ( '20050101', '20050601' )

Choose one of the answers below: 1. 1 partition with data spread across 3 ranges. 2. 2 partitions 3. 3 partitions 4. It depends on how many databases are being used.

Answer: 3. 3 partitions This code actually sets up 3 partitions. One for all datetime values less than Jan 1, 2005 (20050101), one for all values >= Jan 1, 2005 but less than June 1, 2005 (20050601), and one for all values >= June 1, 2005. Ref: CREATE PARTITION - http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-insql-server-2005/

Question 25 - SQL Server 2005 You create a database snapshot of your large production database after hours when no one is using the database. How much data is copied to the snapshot initially? Choose one of the answers below: 1. The entire database is copied. 2. You control how much data is copied with optional parameters specified with the database snapshot. 3. All data pages that are referenced in the current transaction log are copied over. 4. None of the user data is copied.

Answer: 4. None of the user data is copied. A database snapshot is a view of the database at a point in time. No user data is initially copied. changed data pages are copied as they are changed in the source database, but queries against unchanged data pages are made against the source data pages. Ref: How Database Snapshots Work - http://msdn2.microsoft.com/enus/library/ms187054.aspx

Question 26 - SQL Server 2005 In SQL Server 2005, you have setup a database mirror from one server to a database on another server. You want clients to connect to the mirror database for reporting purposes to lesson the load on your primary server. Can you do this? Choose one of the answers below: 1. Yes 2. No

Answer: 2. No You cannot use the mirror database. The mirror database is perpetually in "recovering" mode and is unusable. You could, however, setup a database snapshot from the mirror and use that. Ref: Mirroring States - http://msdn2.microsoft.com/enus/library/ms189284.aspx Database Mirroring and Database Snapshots http://msdn2.microsoft.com/en-us/library/ms175511.aspx

Question 27 - SQL Server 2005 What does this return? select * from INFORMATION_SCHEMA.ROUTINES

Choose one of the answers below: 1. A result set of all startup procedures on the server. 2. A result set of only stored procedures in the current database. 3. A result set of stored procedures and functions in the current database. 4. A list of CLR Assemblies in the current database.

Answer: 3. A result set of stored procedures and functions in the current database. The Routines Information_schema view shows a list of all the stored procedures and functions in the current database that the user has access to. Ref: information_schema.routines - http://msdn2.microsoft.com/enus/library/ms188757.aspx - http://www.databasejournal.com/features/mssql/article.php/3508881

Question 28 - SQL Server 2005 In using a SQL Server 2005 database mirror server to preseve a copy of your production database and prevent data loss, what is the distance limitation between these two servers. Choose one of the answers below: 1. 100 ft 2. 1000 feet 3. same continent 4. No limit

Answer: 4. No limit Since the communcations between the primary and mirror are network based and no equipment is shared, there is no distance limitation for the two servers. Ref: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.msp x Prerequisites and Recommendations for Database Mirroring http://msdn2.microsoft.com/en-us/library/ms366349.aspx

Question 29 - SQL Server 2005 In SQL Server 2005, which of the following is a centralized user interface for managing database servers, Analysis Services, SQL Server CE solutions, and Reporting Services servers? Submitted by Asa Jhunnejah Choose one of the answers below: 1. Business Intelligence Design Studio 2. SQL Server Enterprise Manager 3. SQL Server Management Studio 4. SQL Server Administration Studio

Answer: 3. SQL Server Management Studio The SQL Server Management Studio administration tool is a centralized user interface for managing database servers, Analysis Services, SQL Server CE solutions, and Reporting Services servers. This tool replaces the MMC snap ins (Enterprise Manager, etc.) from SQL Server 2000. Ref: Introducing SQL Server Management Studio http://msdn2.microsoft.com/en-us/library/ms174173.aspx

Question 30 - SQL Server 2005 You are considering SQL Server 2005 Standard edition as an upgrade for your SQL Server 2000 Enterprise server. You will perform a clean install of SQL Server 2005 and then migrate your databases between instances. Your current server is an 8GB, however, so what are your options for ensuring you use all the available RAM? Choose one of the answers below: 1. You must upgrade to SQL Server 2005 Enterprise Edition because you have 8GB of RAM and Standard edition only works with 2GB. 2. You cannot upgrade SQL Server 2000 Enterprise to SQL Server 2005 Standard and must just purchase new SQL Server 2005 licenses. 3. You can upgrade to SQL Server 2005 Standard edition as it will support unlimited RAM.

Answer: 3. You can upgrade to SQL Server 2005 Standard edition as it will support unlimited RAM. SQL Server 2000 Standard edition only supported 2GB of RAM, so if you wanted to use more RAM, you needed to purchase Enterprise Edition. SQL Server 2005 Standard edition, has no RAM limitations, limited by the OS only, so you can just install SQL Server 2005 Standard and use all your RAM. Ref: SQL Server 2005 Features Comparison http://www.microsoft.com/sql/prodinfo/features/comparefeatures.mspx

Question 31 - SQL Server 2005 You have just taken a new job and are administering a SQL Server 2005 database. The developers have a habit of creating tables and not informating you, which causes issues when moving applications into production. What is the easiest way to track new table creation on your development system? Choose one of the answers below: 1. Run profiler continuously on the development system and then search for create table statements in the trace file. 2. There is no way to track when a table is created. 3. Build a web application for the developers to create their tables and log each execution. 4. Create a Data Definition Trigger on the CREATE TABLE statement.

Answer: 4. Create a Data Definition Trigger on the CREATE TABLE statement. While Profiler will work and you can create an application, in SQL Server 2005 there are DDL, Data Definition Language, triggers that you can create on DDL statements. For example, assuming you have created a Logger table: CREATE TRIGGER createtabletrig ON DATABASE FOR CREATE_TABLE AS INSERT logger VALUES(EventData);

which will insert an XML stream of data into the logger table for each create table statement. Ref: Search CREATE TRIGGER in 2005 books online. (http://msdn2.microsoft.com/en-us/library/ms189799(enUS,SQL.90).aspx A shortcut for decoding the XML: http://www.sqlskills.com/blogs/bobb/PermaLink.aspx?guid=c524f5180b4a-49fc-a7b7-b08a3039f7c8

Question 32 - SQL Server 2005 What in the world does @@TIMETICKS return on SQL Server 2005? Choose one of the answers below: 1. Amount of milliseconds the last query required. 2. Amount of milliseconds the last batch required. 3. Number of milliseconds per tick. 4. Number of microseconds per tick.

Answer: 4. Number of microseconds per tick. I am not sure why you would use this, but I'm sure there are applications. And I love the explanation (T-SQL ref, 2005): "The amount of time per tick is computer-dependent. Each tick on the operating system is 31.25 milliseconds, or one thirty-second of a second." Ref: @@TIMETICKS - http://msdn2.microsoft.com/enus/library/ms187738(en-US,SQL.90).aspx

Question 33 - SQL Server 2005 You are looking to make working with the new SQLCMD command line tool easier as you need to check a number of servers for their patch levels. You want it to execute a script to return the version of the server every time you execute SQLCMD. What can you use? Choose one of the answers below: 1. create a script and add it as a parameter to execute when you run SQLCMD. 2. Create a script that returns the information and set the environmental variable sqlcmdini to the path and name of the script file. 3. Set the environmental variable sqlcmdstart to a script that returns the information.

Answer: 2. Create a script that returns the information and set the environmental variable sqlcmdini to the path and name of the script file. If you set a script in the variable "sqlcmdini", then it will execute each time that you run SQLCMD. You can include it as a parameter, but that allows someone to forget to add it. By adding it as a variable, it runs every time. Ref: Using sqlcmd with Scripting Variables http://msdn2.microsoft.com/en-us/library/ms188714.aspx

Question 34 - SQL Server 2005 - Security Which of the following commands will successfully create the TestUser user with a default schema of Development? Submitted by Brian Kelley Choose one of the answers below: 1. CREATE USER TestUser FOR TestUser HAVING DEFAULT_SCHEMA = Development; 2. CREATE USER TestUser FOR TestUser WITH DEFAULT_SCHEMA = Development; 3. CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = Development; 4. CREATE USER TestUser FOR TestUser WITH SCHEMA = Development;

Answer: 3. CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = Development; Option a is incorrect because HAVING is the incorrect syntax. The correct syntax to specify a default schema is WITH DEFAULT_SCHEMA = . This is also why option d is incorrect. The difference between option b and option c is also syntactical in nature. Option b has FOR TestUser instead of FOR LOGIN TestUser like option c. The correct syntax is FOR LOGIN. Omitting the LOGIN statement will result in SQL Server returning an “Incorrect syntax near the keyword ‘with’.” error. Therefore, option c is the only correct answer. Ref: CREATE USER - http://msdn2.microsoft.com/enus/library/ms173463.aspx

Question 35 - SQL Server 2005 - Security What would this statement do in SQL Server 2005? Assume Fred has no permissions on the view prior to this statement. grant view definition on object::authors to 'fred'

Choose one of the answers below: 1. Allows Fred to edit the definition of the view. 2. Allows Fred to query that the object exists. 3. Allows Fred to query the view and return data. 4. The definition keyword is not syntactically valid in SQL Server 2005.

Answer: 2. Allows Fred to query that the object exists. This statement is a security permissions statement that grants Fred the permission to see that the authors object exists. It does not grant him permissions to read the data inside the object. Ref: View Definition Permission - http://msdn2.microsoft.com/enus/library/ms175808.aspx

Question 36 - SQL Server 2005 - Security SQL Server 2005 brings us a number of new encrpytion features. One of them uses a simple mechanism to provide two way encrpytion/decryption. You are responsible for securing your keys for this. Which of the following pairs of functions supports this? Choose one of the answers below: 1. EncryptByPassPhrase() DecryptByPassPhrase() 2. EncryptByPassword() DecryptByPassword() 3. EncryptWithKey() DecryptWithKey() 4. EncryptText() DecryptText()

Answer: 1. EncryptByPassPhrase() DecryptByPassPhrase() There are two new functions, EncryptByPassPhrase() and DecryptByPassPhrase(), that leave the key management to you. Each of these takes a phrase, which can be words, numbers, or plain readable English, and uses that to encrypt plain text or decrypt some values. Note that the encrypted text is returned as a varbinary result. An example: declare @plaintext varchar(1000) , @phrase varchar(40) , @encrypted varbinary( 1000) , @decrypt varchar(1000) select @plaintext = 'This is important information' select @phrase = 'MyEncryptionKey' select @encrypted = EncryptByPassPhrase( @phrase, @plaintext) select @decrypt = DecryptByPassPhrase( @phrase, @encrypted) select @plaintext, @phrase, @encrypted, @decrypt

Ref: SQL Server Language Reference - http://msdn2.microsoft.com/enus/library/ms166026.aspx DecryptByPassPhrase - http://msdn2.microsoft.com/enus/library/ms188910.aspx EncryptByPassPhrase - http://msdn2.microsoft.com/enus/library/ms190357.aspx

Question 37 - SQL Server 2005 - Security In SQL Server 2005, you can enforce various password policies for your users. Can you selectively enforce these policies for individual logins? Choose one of the answers below: 1. Yes 2. Yes, but only for non-administrative users. 3. No

Answer: 1. Yes Yes, each login has on the Properties General tab two checkboxes. One to enforce the Password policy for this user and one to enforce the password expiration for this user. Ref: Password Policy - http://msdn2.microsoft.com/enus/library/ms161959.aspx

Question 38 - SQL Server 2005 - Security In SQL Server 2005, you can create and integrate CLR objects into the database. These objects can be .NET assemblies that are referenced through stored procedures, functions, etc. There are 3 levels of security for each assembly. What are they? Choose one of the answers below: 1. Trusted, Un-Trusted, and Unmanaged 2. Safe, Unsafe, and Unmanaged 3. Safe, Server Access, and Remote Access 4. Safe, External Access, and Unsafe

Answer: 4. Safe, External Access, and Unsafe The three levels of security are Safe, External Access, and Unsafe. The meanings are (as of April 2005):

•

• •

Safe - Only internal computation and local data access is allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. EXTERNAL_ACCESS - SAFE, with the additional ability to access external system resources such as files, networks, environmental variables, and the registry. UNSAFE - UNSAFE allows assemblies unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can call unmanaged code.

Ref: CLR Integration Code Access Security http://msdn2.microsoft.com/en-us/library/ms345101.aspx.

Question 39 - SQL Server 2005 - Security You are working with SQL Server 2005 and wish to secure your database. You can create a key for the database, which will be used to secure all other keys inside this database. What is the way to do this? Choose one of the answers below: 1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Wef9SAkt34FixDT' 2. CREATE KEY MyDatabase @PASSWORD = 'WeZ#6hv*XHq#akAEaqcr7%CUP3aQ' 3. ALTER DATABASE MyDatabase ADD KEY 'Wef9SAkt34FixDT' 4. INSERT master.dbo.syskeys select 'MyDatabase', 'Wef9SAkt34FixDT'

Answer: 1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Wef9SAkt34FixDT' To create a master key for a particular database, you should be using that database and then execute the CREATE MASTER KEY syntax with the password that will be used to encrypt this key. Ref: - http://msdn2.microsoft.com/en-us/library/aa337551.aspx

Question 40 - SQL Server 2005 - Security You have created a certificate for MyUser with this syntax: CREATE CERTIFICATE MyUserCertificate AUTHORIZATION MyUser WITH subject = 'Certificate For MyUser'

Now which is the following functions will use this certificate to encrypt data? Choose one of the answers below: 1. Encrypt(Cert_ID('MyUserCertificate'), @data) 2. EncryptByCert('MyUserCertificate', @data) 3. EncryptByCert(Cert_ID('MyUserCertificate'), @data) 4. Encrypt( MyUserCertificate, @data)

Answer: 3. EncryptByCert(Cert_ID('MyUserCertificate'), @data) Once you have created a certificate, the EncryptByCert function can use this certificate to encrypt data. The certificate ID must be used and this is returned by the cert_id() function. Ref: - http://msdn2.microsoft.com/en-us/library/ms188061.aspx

Question 41 - SQL Server 2005 - Security You are working with SQL Server 2005 and wish to create a certificate for a contractor to use for a limited time. Which one of these answers will do this? Choose one of the answers below: 1. CREATE CERTIFICATE Contractor1Cert AUTHORIZATION Contractor1 WITH subject = 'Certificate For Contract #1', EXPIRY_DATE = '12/31/2005', ENCRYPTION BY PASSWORD = 'q%dsabciJ&#QZk#wM5G!WB36z5m7'

2. CREATE CERTIFICATE Contractor1Cert WITH ENCRYPTION BY PASSWORD = 'q%dsabciJ&#QZk#wM5G!WB36z5m7' GO GRANT AUTHORIZATION TO Contractor1 on Contractor1Cert UNTIL '12/31/2005'

3. CREATE CERTIFICATE Contractor1Cert AUTHORIZATION Contractor1 WITH subject = 'Certificate For Contract #1', ENCRYPTION BY PASSWORD = 'q%dsabciJ&#QZk#wM5G!WB36z5m7' go sp_add_job @start_time='12/31/2004', @cmd='delete Contractor1Cert'

Answer: 1. CREATE CERTIFICATE Contractor1Cert AUTHORIZATION Contractor1 WITH subject = 'Certificate For Contract #1', EXPIRY_DATE = '12/31/2005', ENCRYPTION BY PASSWORD = 'q%dsabciJ&#QZk#wM5G!WB36z5m7'

To limit the time frame for a certificate, you use the EXPIRY_DATE parameter of the CREATE CERTIFICATE command. Ref: - http://msdn2.microsoft.com/en-us/library/ms187798.aspx

Question 42 - SQL Server 2005 - Security Under which account does the SQL Server 2005 Full Text Search service run? Choose one of the answers below: 1. LocalSystem 2. The service account used for the default instance. 3. The service account used for that instance's SQLAgent. 4. The service account used for that instance's SQL Server engine.

Answer: 4. The service account used for that instance's SQL Server engine. In SQL Server 2005, the full-text engine is synchronized to use the same account as the Database Engine (as opposed to LocalSystem), making it is easier to deploy full-text search functionality in a lockeddown environment, yet the side-by-side approach ensures there is no risk of the side effects that can be introduced in a shared-service setting. Ref: SQL Server 2005 Full-Text Search: Internals and Enhancements http://msdn.microsoft.com/en-us/dnsql90/html/sql2005ftsearch.asp

Question 43 - SQL Server 2005 - Security You are working with SQL Server 2005 and decide to use an application role. During your testing, you invoke the application role, only to find a problem. To return to your administrative role, what can you do? Choose one of the answers below: 1. You can only close your session and then reconnect with a new session to fix the app role. 2. You can run sp_unsetapprole, provided that you have saved the output of sp_setapprole in a cookie. 3. You can run sp_unsetapprole and it will return your permissions to your login account. 4. You can run SETUSER and return to your previous login.

Answer: 2. You can run sp_unsetapprole, provided that you have saved the output of sp_setapprole in a cookie. In SQL Server 2005, you can "unset" an application role using sp_unsetapprole, but you must have previously saved a cookie from sp_setapprole. The calls (before and after) would be: DECLARE @myCookie varbinary(256) EXEC sp_setapprole 'myapp', 'PasswordHere', @fCreateCookie = true, @cookie = @myCookie OUTPUT -- You are now running the app role -- now, unset it EXEC sp_unsetapprole @myCookie

Ref: sp_unsetapprole - http://msdn2.microsoft.com/enus/library/ms365415.aspx

Question 44 - SQL Server 2005 - Security You are working with SQL Server 2005 and want to take advantage of the new security policies in your company. Specifically, you are concerned about the consultants that you employ. Your company uses a number of different consultants for short term projects, often calling the same person back for additional work. However there are long lapses between any one person working on your systems. What is the best way to handle this? Choose one of the answers below: 1. Generate scripts for the creation of the logins and associated permissions and scripts for their drop and run these as needed. 2. Run the following for all logins (Steve is a sample login) when they are done working: alter login Steve disable

Run this when they are called back: alter login Steve enable

3. Run the following for all logins (Steve is a sample login) when they are done working: disable login Steve

Run this when they are called back: enable login Steve

4. Run the following for all logins (Steve is a sample login) when they are done working: sp_loginoption('disable','Steve')

Run this when they are called back: sp_loginoption('enable','Steve')

Answer: 2. Run the following for all logins (Steve is a sample login) when they are done working: alter login Steve disable

Run this when they are called back: alter login Steve enable

SQL Server 2005 includes the ability to alter a login and disable it's use for a time and then alter re-enable the login, preserving all permissions. The syntax to disable is: alter login Steve disable

and to re-enable the login alter login Steve enable

Ref: ALTER LOGIN - http://msdn2.microsoft.com/enus/library/ms189828.aspx

Question 45 - SQL Server 2005 - Security User Test1 owns 3 schemas in a SQL Server 2005 database. They are named MyTest, MyOtherTest, and StillAnotherTest. Test1 has their default schema set to MyTest and is a ddladmin. They create a table as follows: create table StillAnotherTest.MySecond ( MyID int , MyChar varchar(10) ) go

The user then executes the following: select * from MySecond

What happens (there is no MySecond table owned by any other schema)? Choose one of the answers below: 1. The user receives a result set of the rows in the MySecond table. 2. The user receives an permissions error because explicit SELECT permissions were not granted to him on this table even though he owns it 3. The user receives an "Invalid Object" error because the schema is not listed. 4. The user receives a list of rows that they have added to the table, but rows added by dbo or others are filtered out.

Answer: 3. The user receives an "Invalid Object" error because the schema is not listed. When a user executes a query with a one-part name, the server checks the following schemas:

• • •

sys the user's default schema dbo

Since this table is not in the user's default schema, a Msg 208, "Invalid Object Name" error is generated. Issuing a "Select * from StillAnotherTest.MySecond" would return all rows from the table. Ref: User-Schema Separation - http://msdn2.microsoft.com/enus/library/ms190387.aspx

Question 46 - SQL Server 2005 - Security User test1 is in the db_ddladmin role, but not db_owner is a SQL Server 2005 database. He executes the following: create table dbo.MySecond ( MyID int , MyChar varchar(10) ) go

What happens? Choose one of the answers below: 1. The user receives an error because they cannot impersonate dbo. 2. The table is created successfully as dbo.MySecond. 3. The table is created as test1.MySecond, as it is substituted for dbo by the server.

Answer: 2. The table is created successfully as dbo.MySecond. The user is a db_ddladmin and can create tables owned by the dbo schema. Ref: - http://msdn2.microsoft.com/en-us/library/ms190667.aspx

Question 47 - SQL Server 2005 - Security New user test1 is a ddl_admin, but not db_owner in a SQL Server 2005 database. This user executes the following: create table dbo.MySecond ( MyID int , MyChar varchar(10) ) go select * from MySecond

What happens? Choose one of the answers below: 1. The table is created as dbo.MySecond and all rows are returned from this table. 2. The table is not created as the user cannot impersonate dbo without being db_owner. 3. The table is created as dbo.MySecond, but the select fails because the user has not been granted SELECT rights on the table. 4. The table is created as test1.MySecond and all rows are returned.

Answer: 3. The table is created as dbo.MySecond, but the select fails because the user has not been granted SELECT rights on the table. While a ddl_admin can create tables owned by the dbo schema, without explicit permissions granted for SELECT, the user cannot actually retrieve data from that table. Ref: User-Schema Separation - http://msdn2.microsoft.com/enus/library/ms190387.aspx

Question 48 - SQL Server 2005 - Security New user test1 has been granted ddl_admin, but not db_owner in a SQL Server 2005 database. The user executes the following: create table dbo.MySecond ( MyID int , MyChar varchar(10) ) go grant select on dbo.MySecond to test1 select * from MySecond

What happens? Choose one of the answers below: 1. The table is created as dbo.MySecond, but the GRANT and SELECT fail. 2. The table is created as dbo.MySecond and the grant and select complete successfully. 3. The table is created as dbo.MySecond and the grant works, but the SELECT fails because it is in the same batch as the grant.

Answer: 1. The table is created as dbo.MySecond, but the GRANT and SELECT fail. The table will be created successfully as the ddl_admin user can create tables owned by the dbo schema. However the user cannot grant permissions to this role and therefore the GRANT will fail. The SELECT will also fail because the user has no permissions on the dbo.MySecond table object. Ref: User-Schema Separation - http://msdn2.microsoft.com/enus/library/ms190387.aspx

Question 49 - SQL Server 2005 - Security You are administering a SQL Server 2005 server and are concerned about unauthorized users on your server. All the acceptable connections from your company should be using Windows authentication for connections. You want to track down any users that are connecting with SQL authentication, however your SP_who2 procedure is corrupt for some reason. How can you track down the SQL authenticated users? Choose one of the answers below: 1. There is not other way. You will have to fix sp_who2. 2. Run select * from sys.dm_exec_connections where auth_scheme = 'sql'

3. Run profiler and look for connection events and note the protocol.

Answer: 2. Run select * from sys.dm_exec_connections where auth_scheme = 'sql'

There are a new set of adminstrative tools called Dynamic Management Views, one of which is dm_exec_connections under the sys schema. This view has a column called auth_scheme that will contain 'NTLM' or 'SQL' depending on the method the connection used. Dynamic Management Views http://www.sqlteam.com/item.asp?ItemID=23040 sys.dm_exec_connections - http://msdn2.microsoft.com/enus/library/ms181509.aspx

Question 50 - SQL Server 2005 - Security User test1 owns the StillAnotherTest schema, which is the default for this user. There are two tables in this database, StillAnotherTest.MySecond and dbo.MySecond, to which user test1 has insert and select permissions. The tables are empty and test1 executes this: insert dbo.MySecond select 1, 'A' insert StillAnotherTest.MySecond select 2, 'B' select * from MySecond

What is returned? Choose one of the answers below: 1. The first insert fails because the user does not own the schema, but the second is completed and "2" and "B" are returned in a two column result set. 2. Both inserts complete and "2" and "B" are returned in a two column result set. 3. Both inserts completeand "1" and "A" are returned in a two column result set.

Answer: 2. Both inserts complete and "2" and "B" are returned in a two column result set. The user has been granted INSERT and SELECT permissions to both tables, so both inserts will complete. The one part name of "MySecond" in the query causes the server to look for tables in this order:

• • •

sys.MySecond StillAnotherTest.MySecond dbo.MySecond

The second entry is the user's default schema.

Question 51 - SQL Server 2005 - Security Which of the following commands is the preferred method in SQL Server 2005 to give access to SQL Server for the Windows account MyDomain\MyUser? Submitted by Brian Kelley Choose one of the answers below: 1. EXEC sp_addlogin ‘MyDomain\MyUser’; 2. CREATE LOGIN [MyDomain\MyUser]; 3. CREATE LOGIN [MyDomain\MyUser] FROM WINDOWS; 4. EXEC sp_grantlogin ‘MyDomain\MyUser’;

Answer: 3. CREATE LOGIN [MyDomain\MyUser] FROM WINDOWS; Of the commands listed, only 3. and 4. are correct. The stored procedure sp_addlogin is used to create a SQL Server login and is not able to give access to a Windows login, hence option 1 is incorrect. Option 2 will return an error because the clause FROM WINDOWS was not included. FROM WINDOWS is required when using CREATE LOGIN if access is being granted to a Windows account. Option 4, the use of sp_grantlogin, is valid and will work in SQL Server 2005. However, this stored procedure is considered deprecated by Microsoft and may be removed in a future release. Microsoft recommends the use of the CREATE LOGIN syntax in order to maintain future functionality. In addition, CREATE LOGIN provides additional functionality such as the ability to set the default database and language. This would require execution of additional stored procedures if sp_grantlogin was used. Therefore, option 3. is the best answer. Ref: - http://msdn2.microsoft.com/en-us/library/ms189751.aspx

Question 52 - SQL Server 2005 - Security Which of the following commands is the Microsoft recommended method of adding a user to a database in SQL Server 2005? Assume a login of NewUser requiring a user name of NewUser. Submitted by Brian Kelley Choose one of the answers below: 1. EXEC sp_addalias ‘NewUser’, ‘NewUser’; 2. EXEC sp_adduser ‘NewUser’, ‘NewUser’; 3. EXEC sp_grantdbaccess ‘NewUser’, ‘NewUser’; 4. CREATE USER NewUser FOR LOGIN NewUser;

Answer: 4. CREATE USER NewUser FOR LOGIN NewUser; While sp_addalias, sp_adduser, and sp_grantdbaccess are still all valid stored procedures to use for adding stored procedure, all three are considered deprecated. Microsoft’s recommendation, per Books Online, is to ensure new development uses the CREATE USER syntax. In addition, CREATE USER provides functionality not found with the other methods. CREATE USER can create a user not tied to a login (meaning it works only in the database), it can create a user for a certificate or symmetric key, and it can set a default schema for the user being created other than a schema with the same name as the user. Therefore, "CREATE USER NewUser FOR LOGIN NewUser;" is the best answer. Ref: CREATE USER - http://msdn2.microsoft.com/enus/library/ms173463.aspx

Question 53 - SQL Server 2005 - Security Which of the following commands will successfully create the TestUser user with a default schema of Development? Submitted by Brian Kelley Choose one of the answers below: 1. CREATE USER TestUser FOR TestUser HAVING DEFAULT_SCHEMA = Development; 2. CREATE USER TestUser FOR TestUser WITH DEFAULT_SCHEMA = Development; 3. CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = Development; 4. CREATE USER TestUser FOR TestUser WITH SCHEMA = Development;

Answer: 3. CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = Development; When specifying a default schema "HAVING DEFAULT_SCHEMA =" is not the correct syntax. To specify a default schema use "WITH DEFAULT_SCHEMA =" . This is also why the option using "WITH SCHEMA" is incorrect. The difference between the two remaining options specifying "WITH DEFAULT_SCHEMA" is also syntactical in nature. The correct syntax is FOR LOGIN . Omitting the LOGIN statement will result in SQL Server returning an "Incorrect syntax near the keyword 'with'." error. Therefore, "CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = Development;" is the only correct option. Ref: CREATE USER - http://msdn2.microsoft.com/enus/library/ms173463.aspx

Question 54 - SQL Server 2005 - Security You want to determine which logins are having password policies and expiration checked on your SQL Server 2005 database server. What table, view, or catalog view would you query against to get this information? Submitted by Brian Kelley Choose one of the answers below: 1. syslogins 2. sysxlogins 3. sys.server_principals 4. sys.sql_logins

Answer: 4. sys.sql_logins The syslogins view is a hold-over from SQL Server 2000 and is included for backward compatibility. As a result, it displays information we’re used to seeing in SQL Server 2000, meaning there is no additional information on password policy or expiration. The sysxlogins table did come over in SQL Server 2005 and any queries against it will result in an “invalid object name” error. While sys.server_principals is a valid catalog view in SQL Server 2005, it does not contain the information on checking password policies and expiration. Only the catalog view sys.sql_logins does. Ref: sys.sql_logins - http://msdn2.microsoft.com/enus/library/ms174355.aspx

Question 55 - SQL Server 2005 - Security You have just executed the command CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P4ssw0rd!'; to create a database master key on the AdventureWorks database and you want to verify that the master key has been created, which query will reveal the database master key? Submitted by Brian Kelley Choose one of the answers below: 1. SELECT * FROM master.sys.asymmetric_keys; 2. SELECT * FROM master.sys.symmetric_keys; 3. SELECT * FROM AdventureWorks.sys.asymmetric_keys; 4. SELECT * FROM AdventureWorks.sys.symmetric_keys;

Answer: 4. SELECT * FROM AdventureWorks.sys.symmetric_keys; The database master key is a symmetric key, meaning you won’t find any information about it in the sys.asymmetric_keys catalog view. Since it is a database master key, the information on the master key is stored in the same database to which they key applies. Although the Books Online documentation states that a copy of the master key is stored both in the user and master databases, you will find information about the database master key within the sys.symmetric_keys catalog view in the database itself. In this case, since the database master key was created on the AdventureWorks database, we would look in the catalog view sys.symmetric_keys within the AdventureWorks database itself. Ref: sys.symmetric_keys - http://msdn2.microsoft.com/enus/library/ms189446.aspx

Question 56 - SQL Server 2005 - Security How secure are encrypted stored procedures, triggers and views? Choose one of the answers below: 1. They are not really secure, they are only obfuscated. There are tools that you can use to get at the code. 2. In SQL Server 2005 the encryption is total and complete and can never be decoded. 3. The code is encrypted using a user-defined hash algorithm. 4. The code is encrypted using a SQL Server defined hash algorithm with the details of the key used returned when the stored procedure etc is created.

Answer: 1. They are not really secure, they are only obfuscated. There are tools that you can use to get at the code. The code itself is just obfuscated. However, you cannot edit the code, the stored procedure or view would have to be dropped and re-created. Using a DDL trigger that executes on object creation, you can tell when it has been modified. You can find the answer on page 311 of Beginning SQL Server 2005 for Developers: From Novice to Professional. Ref: CREATE PROCEDURE - http://msdn2.microsoft.com/enus/library/ms187926(SQL.90).aspx

Question 57 - SQL Server 2005 - SMO, RMO, AMO In SQL Server 2005, SMO has replaced DMO as the management object framework. SMO is developed with .NET. Can you still call SMO objects from VBScript? Choose one of the answers below: 1. Yes, but you would need to wrap your VBScript in a .NET wrapper. 2. No, you cannot use VBScript any longer. 3. Yes, there are COM wrappers around the .NET assemblies. 4. No, there is no way to access these objects from non-.NET enabled code.

Answer: 3. Yes, there are COM wrappers around the .NET assemblies. Yes, you can still call these objects from VBScript. You do not call the .NET assemblys directly. Instead you would reference the .COM wrappers that are placed around each assembly. There is a SQLSMO.dll file that allows COM access. Ref: SMO Overview - http://msdn2.microsoft.com/enus/library/ms162557.aspx

Question 58 - SQL Server 2005 - SMO, RMO, AMO In the new management object framework for SQL Server 2005, there are new categories of classes: instance and utility classes. Various functions and information has been moved around. Which of the following are valid classes? Choose one of the answers below: 1. Scripter 2. Generator 3. JobExec 4. DBBackup

Answer: 1. Scripter The utility classes have been added to handle certain tasks. There are Transfer classes, Backup and Restore classes, and the Scripter classes. In the Instance classes, there are still the Server, Database, User, Table, etc. classes. The other classes listed below are not valid. Ref: SMO Classes - http://msdn2.microsoft.com/zhcn/library/ms199467.aspx Scripter Class - http://msdn2.microsoft.com/enus/library/microsoft.sqlserver.management.smo.scripter.aspx

Question 59 - SQL Server 2005 - SMO, RMO, AMO You wish to build an application in VB.NET that will keep track of and manage some of your SQL Server 2005 replication configurations. Which namespace in SMO would you use? Choose one of the answers below: 1. Microsoft.SqlServer.Management.Smo.Replication 2. Microsoft.SqlServer.Smo.Management.Replication 3. Microsoft.SqlServer.Smo.Replication.Management 4. You cannot do this using SMO.

Answer: 4. You cannot do this using SMO. This is a bit of a trick question. SMO (System Management Objects) in SQL Server 2005 replaces the DMO object framework in SQL Server 2000. However replication is not included in this framework. The replication object framework has been moved to RMO (Replication Management Objects). Ref: Replication Management Objects http://msdn2.microsoft.com/en-us/library/ms148076.aspx

Question 60 - SQL Server 2005 - SMO, RMO, AMO What does this snippet of SMO code do? this.Cursor = Cursors.WaitCursor; // Fill the databases combo comboDB.Items.Clear(); PropertyGridDB.SelectedObject = null; Server SelectedServer = new Server(comboServer.Text); Int32 DBCount = 0; foreach (Database db in SelectedServer.Databases) { if (CheckSystemDB.Checked) { DBCount++; comboDB.Items.Add(db.Name); } else if (!db.IsSystemObject) { DBCount++; comboDB.Items.Add(db.Name); } } if (DBCount > 0) { lblDB.Text = "&Database - " + DBCount + " found. Select one from the following list:"; comboDB.Enabled = true; } else lblDB.Text = "&Database"; CheckSystemDB.Enabled = true;

Choose one of the answers below: 1. This code counts the number of user databases on a server and returns that to the user. 2. This code counts the number of databases on a server and fills a combo box with the names of the databases. 3. This code creates a new database on the SQL Server. 4. This code will get a list of databases for the user and detach them one by one.

Answer: 2. This code counts the number of databases on a server and fills a combo box with the names of the databases. This snippet of code will use the collection of databases in the SMO object to get a list of the names of each from the server. A combo box is populated with the names and the number of databases found, and these are returned in a label.

Question 61 - SQL Server 2005 - SMO, RMO, AMO SQL Server 2005 has implemented SMO as a replacement for DMO. This new framework includes a new switch that enables capture mode for the commands run by the SMO application. What does this do? Choose one of the answers below: 1. It allows you to control Profiler from SMO. 2. It captures all the SMO statements that your SMO application emits. 3. It captures all the statements that the SMO code emits to the server. 4. It logs an entire transcript of the TDS connection as long as enabled.

Answer: 3. It captures all the statements that the SMO code emits to the server. From the 2005 BOL: SMO programs can capture and record the equivalent Transact-SQL statements issued by the program in place of, or in addition to, the statements that are executed by the program. Ref: Using Capture Mode - http://msdn2.microsoft.com/enus/library/ms162182.aspx

Question 62 - SQL Server 2005 - SSIS You are working with SQL Server 2005 and would like to view the event logs from Integration Services using the Event Viewer in Windows. How do you do this? Choose one of the answers below: 1. Use the Event Viewer and select SSIS Logs in the left hand pane below Application Logs, Security Logs, and System Logs. 2. You cannot access the Integration Services event logs in Event Viewer. 3. In the Event Viewer, under the Application logs, set a filter for "SSIS" as the source. 4. In the Event Viewer, under the Application logs, set a filter for "SQLISService" as the source.

Answer: 4. In the Event Viewer, under the Application logs, set a filter for "SQLISService" as the source. You can view the Integration Services event logs by running the Windows Event Viewer and setting a source filter on "MsDtsServer". Ref: View Event Logs for Integration Services Service Using the Event Viewer - http://msdn2.microsoft.com/en-us/library/ms137978.aspx

Question 63 - SQL Server 2005 - SSIS In SQL Server 2005 Integration Services, what does the derived column transformation do? Choose one of the answers below: 1. This transformation will run an aggregate query between a source and a destination. 2. This transformation will allow you to derive a column using functions from the source as a new column for the destination. 3. This transformation returns only a substring of a column from a source nd moves it to a destination. 4. The transformation only allows you to rename a source column to insert it into the destintation.

Answer: 2. This transformation will allow you to derive a column using functions from the source as a new column for the destination. This transformation will allow you to take one or more columns and derive a new value by running a function on the source column prior to inserting it into the destination. Ref: SQL Server Integration Services - Part 3 http://www.databasejournal.com/features/mssql/article.php/3509601 Derived Column Transformation - http://msdn2.microsoft.com/enus/library/ms141069.aspx

Question 64 - SQL Server 2005 - SSIS In SQL Server 2005 Integration Services, the connection information is stored where? Choose one of the answers below: 1. In the Connection Manager. 2. Inside each data flow task. 3. Inside the control flow container for a data flow. 4. It is stored in a global variable collection.

Answer: 1. In the Connection Manager. The connection information for each connection is stored in the connection manager. Ref: The New ETL Paradigm http://www.sqlservercentral.com/columnists/jthomson/thenewetlparadi gm.asp

Question 65 - SQL Server 2005 - SSIS What is late validation in SQL Server 2005 Integration Services? Choose one of the answers below: 1. It is the process of checking the data values before a task executes to be sure that there are no data type conflicts. 2. It is the process of checking the package for syntax errors. 3. It is the process of the Execute() method for a component in the package calling the Validate() method to be sure that there isn't anything obvious that would cause the component to fail execution. 4. It is the process of a developer running a SSIS Profile on the package to spot areas of poor performance.

Answer: 3. It is the process of the Execute() method for a component in the package calling the Validate() method to be sure that there isn't anything obvious that would cause the component to fail execution. Validation is what a component does to detect any issues that would cause it to fail during execution. The Validate() method is called by the Execute() method for a component. Ref: What is Validation? - http://www.sqlis.com/default.aspx?51

Question 66 - SQL Server 2005 - SSIS In SQL Server 2005 Integration Services, when you execute a child process from a parent does it run in process or out of process? Choose one of the answers below: 1. In Process 2. Out of Process 3. Either 4. Neither

Answer: 3. Either The answer is either. You can specify this with the ExecuteOutOfProcess property. Ref: ExecutePackageTaskClass.ExecuteOutOfProcess Property http://msdn2.microsoft.com/enus/library/microsoft.sqlserver.dts.tasks.executepackagetask.executepac kagetaskclass.executeoutofprocess.aspx

Question 67 - SQL Server 2005 - SSIS In SQL Server 2005, are SQL Server Integration Services able to be clustered? Choose one of the answers below: 1. Yes 2. No

Answer: 1. Yes In SQL Server 2005, you can make the SSIS services clustered, either in the same group as SQL Server or a separate group. Ref: How to: Configure Integration Services on a Cluster http://msdn2.microsoft.com/en-us/library/ms345193.aspx Making SSIS a Clustered Service http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015. aspx.

Question 68 - SQL Server 2005 - SSIS How can you execute a DTS package in SQL Server 2005 Integration Services using the local server, but loading the package from a remote server? Choose one of the answers below: 1. You cannot do this. Only packages in the local server MSDB database can be executed against this server. 2. You can only do this by replicating packages between MSDB databases. 3. Alter the MsDtsSrvr.ini.xml configuration file to point to the remote server's MSDB database.

Answer: 3. Alter the MsDtsSrvr.ini.xml configuration file to point to the remote server's MSDB database. If you alter the MsDtsSrvr.ini.xml configuration file, you can have the package execute against the local server, which is preferable when deploying packages, but the local server will read the package from the remote server. Ref: Little Known SSIS Features http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16023. aspx

Question 69 - SQL Server 2005 - SSIS In SQL Server 2005, if you have a data-flow task in your package, which event handler system variable provides the time that the data-flow started to execute? Choose one of the answers below: 1. System::StartTime 2. System::ContainerStartTime 3. System::EventHandlerStartTime 4. You can't do this

Answer: 2. System::ContainerStartTime Answer: #2. Explanation: System::ContainerStartTime contains the time that the container that raised the event began to execute. When a data-flow task executes the container in question is a TaskHost container. There is more information here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/07/ 15755.aspx and here: http://blogs.conchango.com/jamiethomson/archive/2004/12/13/445.asp x

Question 70 - SQL Server 2005 - SSIS SSIS can store the multi-row results of a SQL query issued from an Execute SQL Task in a variable. What must the datatype of that variable be? Submitted by Jamie Thomson Choose one of the answers below: 1. XML 2. String 3. Object 4. Resultset

Answer: 3. Object Early versions of BOL stated that this should be stored in a String variable. This is erroneous and has been corrected. There are no such things as XML variables or ResultSet variables in SSIS. Ref: Execute SQL Task - http://msdn2.microsoft.com/enus/library/ms141003.aspx

Question 71 - SQL Server 2005 - SSIS Which of the following is true? Submitted by Jamie Thomson. Choose one of the answers below: 1. A Synchronous Script Transform (SST) always outputs the same number of rows that are passed to it. 2. A Synchronous Script Transform (SST) never outputs more rows than are passed to it. 3. A Synchronous Script Transform (SST) never outputs less rows than are passed to it. 4. There is no restriction on the number of rows output from a synchronous script component.

Answer: 4. There is no restriction on the number of rows output from a synchronous script component. If such a component has more than 1 output we can choose to output an incoming row to none, some or all of the outputs. Ref: Creating a Synchronous Transformation with the Script Component - http://msdn2.microsoft.com/en-us/library/ms136114.aspx

Question 72 - SQL Server 2005 - SSIS Which of the following is an asynchronous data-flow component, meaning it consumes the entire rowset, rather than operates row by row? Submitted by Jamie Thomson Choose one of the answers below: 1. Audit 2. Aggregate 3. Conditional Split 4. Derived Column

Answer: 2. Aggregate The Aggregate transformation is asynchronous, which means it does not consume and publish data row by row. Instead it consumes the whole rowset, performs its groupings and aggregations, and then publishes the results. Ref: Aggregate Transformation = http://msdn2.microsoft.com/enus/library/ms191497.aspx Audit Transformation - http://msdn2.microsoft.com/enus/library/ms141150.aspx Conditional Split - http://msdn2.microsoft.com/enus/library/ms137886.aspx Derived Column Transformation - http://msdn2.microsoft.com/enus/library/ms141069.aspx

Question 73 - SQL Server 2005 - SSIS Which component enables you to select the first N rows that pass through your data-flow and discard the rest? Choose one of the answers below: 1. Script 2. Conditional Split 3. Data Conversion 4. Pivot

Answer: 1. Script The script component will allow you to selec the first N rows and discard the rest. Ref: Script comonent - http://msdn2.microsoft.com/enus/library/ms137640.aspx

Question 74 - SQL Server 2005 - SSIS Suppose you have a script component with 2 synchronous outputs in the same ExclusionGroup called "MyOutput1" & "MyOutput2". Which of the following 2 methods are generated for you automatically? Choose one of the answers below: 1. PassToOutputMyOutput1() & PassToOutputMyOutput2() 2. DirectRowToMyOutput1() & DirectRowToMyOutput2() 3. DirectToOutputMyOutput1() & DirectToOutputMyOutput2() 4. PassRowToMyOutput1() & PassRowToMyOutput2()

Answer: 2. DirectRowToMyOutput1() & DirectRowToMyOutput2() Explanation (From BOL) "During execution, data flow components that have multiple outputs with the same synchronous input and the same nonzero exclusion group direct rows from the input PipelineBuffer to one of the outputs using the DirectRow method." What BOL fails to say is that the DirectRowTo() methods are created for you as well. Ref: Creating a Synchronous Transformation with the Script Component - http://msdn2.microsoft.com/zh-cn/library/ms136114.aspx Understanding Synchronous and Asynchronous Transformations Understanding Synchronous and Asynchronous Transformations

Question 75 - SQL Server 2005 - SSIS Package configurations get applied during package loading time and not at the package execution time with one exception. Which package configuration gets applied at execution time? Submitted by Ashwani Nanda Choose one of the answers below: 1. SQL Server 2. XML configuration file 3. Parent package variable 4. Environment variable

Answer: 3. Parent package variable It is actually Parent Package variable configuration for which the parent package has to execute to update parent package variable before it can be used in the child package configuration. Ref: How to: Use Values of Parent Variables in Child Packages http://msdn2.microsoft.com/en-us/library/ms345179.aspx

Question 76 - SQL Server 2005 - SSIS What method do you have to call in an asynchronous script component to allow you to add rows to an output called "Output 0"? Submitted by Jamie Thomson Choose one of the answers below: 1. Output0.AddRow() 2. Output_0.Addrow() 3. Output0Buffer.AddRow() 4. Output0Buffer.DirectRowToOutput0

Answer: 3. Output0Buffer.AddRow() 3 is the correct syntax for this. 1 & 2 are invalid syntax. The DirectRowTo() syntax in 4 is used in synchronous script components. Ref: Creating an Asynchronous Transformation with the Script Component - http://msdn2.microsoft.com/en-us/library/ms136133.aspx

Question 77 - SQL Server 2005 - SSIS Which of the following statements most accurately describes the difference between synchronous and asynchronous script components? Submitted by Jamie Thomson Choose one of the answers below: 1. "A synchronous script component outputs the same number of rows as the input. This probably will not be the case with a asynchronous script component" 2. "A synchronous script component uses the same buffer for the input as it does for the output. An asynchronous component does not." 3. "An asynchronous script component requires all the rows from an upstream component before it can start to process them. A synchronous script component does not" 4. "A synchronous script component will run quicker than an asynchronous script component that carries out the same task"

Answer: 2. "A synchronous script component uses the same buffer for the input as it does for the output. An asynchronous component does not." 1, 3 & 4 are usually the case, but not always. 2 is always true. Ref: Understanding Synchronous and Asynchronous Transformations http://msdn2.microsoft.com/en-us/library/aa337074.aspx

Question 78 - SQL Server 2005 - SSIS I have a script component that has ReadOnlyVariables="foovar". How do I reference the value in variable foovar in my script? Submitted by Jamie Thomson Choose one of the answers below: 1. Dts.Variables("foovar").value 2. Dts.Variables.foovar 3. Dts.Variables("user::foovar").value 4. Me.Variables.foovar

Answer: 4. Me.Variables.foovar The correct reference uses the self referencing object "Me". Ref: Comparing the Script Task and the Script Component http://msdn2.microsoft.com/en-us/library/ms136031.aspx

Question 79 - SQL Server 2005 - SSIS In which languages can SQL Server 2005 Integration Services script components be built? Submitted by Jamie Thomson Choose one of the answers below: 1. C# 2. VB.NET 3. C# & VB.Net 4. Any .Net language

Answer: 2. VB.NET The script component relies on Visual Studio for Applications (VSA) which currently only supports VB.Net. Ref: Extending the Data Flow with the Script Component http://msdn2.microsoft.com/en-us/library/ms136118.aspx Integration Services Programming (see note at bottom of language section) - http://msdn2.microsoft.com/zh-tw/library/ms136025.aspx

Question 80 - SQL Server 2005 - SSIS Which of the following is the signature of the method called to process all the incoming rows in a script component? Submitted by Jamie Thomson Choose one of the answers below: 1. Input0_ProcessInputRow() 2. Input0_ProcessInputRow(ByVal Row As IDTSInput90) 3. Input0_ProcessInputRow(ByVal Row As Input0Buffer) 4. Input0_ProcessInputRow(ByVal Row As InputBuffer)

Answer: 3. Input0_ProcessInputRow(ByVal Row As Input0Buffer) 3 gives the correct syntax for the script component. The method _ProcessInputRow(...) takes one parameter which is the row currently being processed. The type of that parameter is the type of a class generated at design-time that is named by taking the name of the input (in this case "Input 0") removing whitespace, and appending the word "Buffer". Hence, in this case because we have an input called "Input 0" or "Input0" (which we know we have because of the name of the method), the type of the parameter is "Input0Buffer". And just to re-iterate the point, the type of the parameter (excepting the word "Buffer") will be the same as the begining of the method name up to the underscore. Ref: Extending the Data Flow with the Script Component http://msdn2.microsoft.com/en-us/library/ms136118.aspx

Question 81 - SQL Server 2005 - SSIS Which SSIS pipeline datatype should you use to store datetime values with days, hours and seconds specified in them? Submitted by Jamie Thomson Choose one of the answers below: 1. DT_TIME 2. DT_DATE 3. DT_DBTIMESTAMP 4. DT_DBDATE

Answer: 3. DT_DBTIMESTAMP DT_TIME only stored hours minutes and seconds. DT_DBDATE only stores years, months and days. DT_DATE and DT_DBTIMESTAMP can both store years down to milliseconds but they have different internal representations and DT_DBTIMESTAMP is more reliable than DT_DATE. More info here: http://blogs.conchango.com/jamiethomson/archive/2005/11/15/2399.as px - http://msdn2.microsoft.com/en-us/library/ms141036.aspx

Question 82 - SQL Server 2005 - SSIS Where should you place your SSIS package templates if you want them made available to you as a basis for new packages? Submitted by Jamie Thomson Choose one of the answers below: 1. %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformation Project\DataTransformationItems 2. %homepath%\my documents\Visual Studio\ProjectTemplates 3. %homepath%\my documents\Visual Studio 2005\ProjectTemplates 4. %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\Visual Studio 2005ProjectTemplates

Answer: 1. %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformation Project\DataTransformationItems No explanation except simply, that’s where you have to put them. A bit more info here: http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.as px How to create a package template in SQL Server Business Intelligence Development Studio - http://support.microsoft.com/kb/908018

Question 83 - SQL Server 2005 - SSIS Which of these is not a configuration type in SSIS? Choose one of the answers below: 1. XML File 2. Text file 3. Registry Entry 4. Environment variable

Answer: 2. Text file SSIS does not allow configurations to be formatted in free format text files. There is some more info here on using environment variables and XML configuration files together: http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.as px Package Configurations - http://msdn2.microsoft.com/enus/library/ms141682.aspx

Question 84 - SQL Server 2005 - TSQL SQL Server 2005 has a number of T-SQL enhancements, including the ability to generate delimited lists. If I want to return the following numeric values: 1;2;3;4;5; from a single integer column table (MyTable) with the values as follows: MyID ----1 2 3 4 5

which of the following will accomplish this: Choose one of the answers below: 1. select MyID + ';' from MyTable for xml path('') 2. select MyID as "text()" + ';' as "text()" from MyTable for xml path('') 3. select MyID as "text()", ';' as "text()" from MyTable for xml path('') 4. select MyID + ';' as "text()" from MyTable for xml path('')

Answer: 3. select MyID as "text()", ';' as "text()" from MyTable for xml path('') The for XML addition to the select statement has been extended to allow for string valued lists. The FOR XML PATH('') clause will return a stringed list, adding in the second column will allow for a delimited list. Ref: FOR Clause - http://msdn2.microsoft.com/enus/library/ms173812.aspx

Question 85 - SQL Server 2005 - TSQL Using SQL Server 2005, you want to create a copy of your database quickly and easily. The source database is called Sales and the copy should be Sales_QA. How do you do this? Choose one of the answers below: 1. copy database sales to sales_qa on disk with 'sales_data' = 'z:\mssql\sales_data.mdf', 'sales_log' = 'z:\mssql\sales_log.ldf' 2. create database sales_qa on (name sales_data, filename = 'z:\mssql\sales_data.mdf') , (name = sales_log, filename = 'z:\mssql\sales_log.ldf') as snapshot of sales; 3. create database sales_qa as snapshot of sales, files = same; 4. create snapshot of sales as sales_qa on (name sales_data, filename = 'z:\mssql\sales_data.mdf') , (name = sales_log, filename = 'z:\mssql\sales_log.ldf')

Answer: 2. create database sales_qa on (name sales_data, filename = 'z:\mssql\sales_data.mdf') , (name = sales_log, filename = 'z:\mssql\sales_log.ldf') as snapshot of sales; A database snapshot is a transactionally consistent copy of another database. The Create Database T-SQL statement has been enhanced to allow this. The syntax is the addition of the "AS SNAPSHOT OF" option. Ref: Database Snapshots - http://msdn2.microsoft.com/enus/library/ms175158.aspx

Question 86 - SQL Server 2005 - TSQL In SQL Server 2005, the SMO framework is built as a .NET assembly. Will this VBScript work? set oServer = CreateObject(“SQLSMO.Server”) Wscript.Echo oServer.Name & “ “ & oServer.Information.VersionString

Choose one of the answers below: 1. No, .NET assemblies cannot be called from VBScript. A .NET executable would be needed. 2. Yes, you can call a .NET assembly. 3. Yes, this will work, but you cannot call a .NET assembly from VBScript. Some wrapper is needed around the .NET assembly. 4. No, VBScript is not supported.

Answer: 3. Yes, this will work, but you cannot call a .NET assembly from VBScript. Some wrapper is needed around the .NET assembly. Yes, this script will work. There is a COM wrapper around the .NET assembly and you can reference that DLL in VBScript. A .NET assembly cannot be directly called from VBScript. See SQL Server 2005 Books Online.

Question 87 - SQL Server 2005 - TSQL What does this function do? CREATE FUNCTION dbo.xxx ( @InputString TEXT, @SubString VARCHAR(200), @NoisePattern VARCHAR(20) ) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN ( SELECT COUNT(*) FROM dbo.Numbers N WHERE SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString AND PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number + LEN(@SubString), 1)) = 0 AND PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number - 1, 1)) = 0 ) END

Choose one of the answers below: 1. Removes @NoisePattern from @InputString if contained inside @Substring 2. Replaces @NoisePattern with @Substring inside @Inputstring 3. Counts the number of times @NoisePattern occurs within @InputString but not in @Substring 4. Counts the number of times @Substring occurs within @Inputstring

Answer: 4. Counts the number of times @Substring occurs within @Inputstring This substring counts the number of occurrences of a substring within a string. Ref: Counting occurrences of a substring within a string http://sqljunkies.com/WebLog/amachanic/articles/CountSubstring.aspx String Manipulation Workbench - http://www.simpletalk.com/sql/learn-sql-server/robyn-pages-sql-server-stringmanipulation-workbench/

Question 88 - SQL Server 2005 - TSQL SQL Server 2005 adds recursive queries as a T-SQL enhancement. A recursive query is made up of how many select statements? Choose one of the answers below: 1. 1 2. 3 3. 2 4. Limited to 128

Answer: 2. 3 A recursive query is made up of 3 SELECT statements. There will be two internal SELECT statements joined by a UNION ALL. The first statement is called the anchor member and it forms the basic result set. The second statement is called the recursive member and this is where the CTE references itself. The recursive member forms the next part of the result set using the anchor member as input. The recursive member is called repeatedly until no more rows are returned (called the exit condition). There is a third outer SELECT that is the one that returns the data to the caller. Ref: Recursive Queries Using Common Table Expressions http://msdn2.microsoft.com/en-us/library/ms186243.aspx Common Table Expressions - http://www.simple-talk.com/sql/sqlserver-2005/sql-server-2005-common-table-expressions/

Question 89 - SQL Server 2005 - TSQL You have an extremely long (> 100 lines) stored procedure that has given you problems in the past because of recompilations due to changing conditions on the server. Which of the following enhancements in SQL Server 2005 should provide a performance benefit to your system? Choose one of the answers below: 1. Snapshot Isolation 2. DDL Triggers 3. Common Table Expressions 4. Statement Level Recompile

Answer: 4. Statement Level Recompile The Statement Level Recompilation feature, which allows the recompilation of a portion of a query plan instead of the entire thing can improve performance in this case. Ref: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Question 90 - SQL Server 2005 - TSQL What would this match in a regular expression? ^(com|net|edu|org)

Choose one of the answers below: 1. "sqlservercentral.com" 2. anything containing "com" but not "net", "edu", or "org" 3. "edu" or "com" or "net" or "org" 4. a string that contains all four domains.

Answer: 3. "edu" or "com" or "net" or "org" The parens and the | indicate an OR matching of any of the items listed between the |. In this case, without anything before or after, this expression would only match "com" by itself or any of the others by themselves. Ref: Regular Expressions Quick Start - http://www.regularexpressions.info/quickstart.html

Question 91 - SQL Server 2005 - TSQL What will be the output of this piece of T-SQL code? Submitted by Frederik Vandeputte BEGIN TRY DROP master.sys.objects END TRY BEGIN CATCH SELECT 'The following error occured: ' + ERROR_MESSAGE() END CATCH

Choose one of the answers below: 1. The following error occured: Incorrect syntax near '.'. 2. The following error occured: Cannot drop the table 'master.sys.objects', because it does not exist or you do not have permission. 3. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '.'.

Answer: 3. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '.'. The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct: Compile errors, such as syntax errors, that prevent a batch from executing. Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution. These errors are returned to the level that ran the batch, stored procedure, or trigger. More info on TRY .. CATCH in Books Online: http://msdn2.microsoft.com/en-us/library/ms175976.aspx

Question 92 - SQL Server 2005 - XML In SQL Server 2005, how are string comparisons made with XML data? Choose one of the answers below: 1. Using the columns' collation 2. Using a binary collation 3. Using the server default collation

Answer: 2. Using a binary collation String comparions for XML data in SQL Server 2005 occur using a binary collation for speed and efficiency. Ref: XML Support in Microsoft SQL Server 2005 http://msdn2.microsoft.com/en-us/library/ms345115.aspx Indexes on xml Data Type Columns - http://msdn2.microsoft.com/enus/library/ms191497.aspx

Question 93 - SQL Server 2005 - XML Which query will give you a list of XML indexes and the tables they are against? Choose one of the answers below: 1. SELECT sxi.name "Index", so.name "Table" FROM sys.xml_indexes sxi INNER JOIN sys.objects so ON (so.object_id = sxi.object_id) WHERE using_xml_index_id IS NULL;

2. SELECT sxi.name "Index", so.name "Table" FROM sysindexes sxi INNER JOIN sys.objects so ON (so.object_id = sxi.object_id) WHERE using_xml_index_id IS NULL;

3. SELECT sxi.name "Index", so.name "Table" FROM sysxmlindexes sxi INNER JOIN sys.objects so ON (so.object_id = sxi.object_id) WHERE using_xml_index_id IS NULL;

4. SELECT sxi.name "Index", so.name "Table" FROM INFORMATION_SCHEMA.INDEXES WHERE using_xml_index_id IS NULL;

Answer: 1. SELECT sxi.name "Index", so.name "Table" FROM sys.xml_indexes sxi INNER JOIN sys.objects so ON (so.object_id = sxi.object_id) WHERE using_xml_index_id IS NULL;

The first query will actually scan the xml_indexes table and join it to sysobjects to find a list of tables that have XML indexes built on them. Ref: sys.xml_indexes - http://msdn2.microsoft.com/enus/library/ms176003.aspx Indexes on xml Data Type Columns - http://msdn2.microsoft.com/enus/library/ms191497.aspx

Question 94 - SQL Server 2005 - XML Which of the following queries will preserve the whitespace in an XML document? Choose one of the answers below: 1. DECLARE @MyXML XML SET @MyXML = CONVERT (XML,' Data b ',1) SELECT @MyXML 2. DECLARE @MyXML XML SET @MyXML = ' MyData ' SELECT @MyXML 3. DECLARE @MyXML XML SET @MyXML = ' MyData ' SELECT Preserve(MyXML) 4. DECLARE @MyXML XML SET @MyXML = ' MyData ' SELECT cast( @MyXML as document)

Answer: 1. DECLARE @MyXML XML SET @MyXML = CONVERT (XML,' Data b ',1) SELECT @MyXML If you use the Convert command on an XML document with the option 1, you can preserve the whitespace (formatting) that exists in the document. The first answer returns: Data b

while the second returns: MyData Note: You need to return results in text from SQL Server Management Studio to see this behavior. Ref: CAST and CONVERT - http://msdn2.microsoft.com/enus/library/ms187928.aspx

Question 95 - SQL Server 2005 - XML You have the following TSQL code in SQL Server 2005: DECLARE @XML xml Set @XML=' '

What does this return? Select @XML.exist('/Features')

Choose one of the answers below: 1. 0 2. 1 3. An error as there is no assembly in the default SQL CLR that will handle the .exist method. 4. False because database mirroring is turned off by default.

Answer: 2. 1 This will return a one row, one column result set with a 1 as this particular class exists in the XML document. This allows you to query the native XML store in SQL Server to find out if a particular element exists in the document before you query for it's attributes or child elements. Ref: exist() Method - http://msdn2.microsoft.com/enus/library/ms189869.aspx

Question 96 - SQL Server 2005 - XML What is a Primary XML Index in SQL Server 2005? Choose one of the answers below: 1. Always the first XML index on an XML datatype column. 2. A shredded and persisted representation of the XML BLOB. 3. Both of the above. 4. No such thing, this is a trick question.

Answer: 3. Both of the above. The Primary XML index is actually a real thing and is the first XML index created. You can create secondary XML indices after this one. It is a shredded and persisted representation of the XML blog that produces data to enable faster searching of the XML data. Ref: Indexes on xml Data Type Columns http://msdn2.microsoft.com/en-us/library/ms191497.aspx

Question 97 - SQL Server Development You build an assembly that exposes a method for validing a phone number in .NET and add it to your SQL Server 2005 server. You then build a user defined function that calls this assembly. If you make a change to the assembly and rebuild it, do you need to recompile your function in SQL Server to see the new changes? Choose one of the answers below: 1. Yes, the reference will still work, but will not see the new changes until you recompile it. 2. Of course, if you change the assembly, you need to rebind the function to the assembly or your code will all fail. 3. No, it will automatically work.

Answer: 1. Yes, the reference will still work, but will not see the new changes until you recompile it. You do not need to recompile the function for it to work. The reference to the assembly will still work, but will call the old assembly. To see the new changes, you would need to recompile your function. Ref: Altering an Assembly - http://msdn2.microsoft.com/enus/library/ms345098.aspx Implement User-defined Functions in SQL Server 2005 with Managed Code http://www.developer.com/net/csharp/article.php/10918_3399881_2

Question 98 - SQL Server Development You wish to store XML documents in SQL Server 2005. What is the best data type to use and still have the data available for querying? Choose one of the answers below: 1. varchar(max) 2. varbinary(max) 3. xml

Answer: 3. xml The best option is the XML data type. This datatype shreds XML and stores it in an internal binary format for easy querying. Ref: XML Options in Microsoft SQL Server 2005 http://msdn.microsoft.com/en-us/dnsql90/html/sql2k5xmloptions.asp XML data type - http://msdn2.microsoft.com/enus/library/ms189887.aspx

Question 99 - T-SQL What could be the output of this query? Declare

@intVal1 Int, @intVal2 Int, @Result Numeric(9,2) Select @intVal1 = 10, @intVal2 = 3 Select @Result = @intVal1/@intVal2 print @Result

Choose one of the answers below: 1. 3.30 2. 3.33 3. 3.00 4. 4.00 5. Error

Answer: 3. 3.00 The data type precedence rules defined that when integer value is divided by an integer value, the result of that process will be an integer. Hence your result will be 3.00

Question 100 - Tools You want to script the execution of an Integration Services package from the command line for use from a Unix scheduler. What utility would you use? Choose one of the answers below: 1. ssisexec.exe 2. dtsexec.exe 3. dtexec.exe 4. dtutil.exe

Answer: 3. dtexec.exe The dtexec.exe utility is used to configure and execute Integration Services packages from the command line. Ref: dtexec.exe - http://msdn2.microsoft.com/enus/library/ms162810.aspx