MS 70-431

Which upgrade data-movement method requires that users not be accessing the database you want to upgrade? A. Copy Database Wizard B. Detach/attach C. Backup/restore D. Manual scripting

B

You are working as a DBA at the Cape Town office of Company.com. Company.com use a SQL Server 2005 database that does not contain any views. You use Database Engine Tuning Advisor (DTA) to tune this database. A workload file that is suitable for DTA already exists. You are required to locate only missing nonclustered indexes. During this process, you need to insure that existing structures remain intact, and that newly recommend structures are partitioned for best performance. You want to accomplish this goal by configuring the tuning options in DTA. Which tuning options should you use?***

***

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability of other data in the database. What should you do? A. Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database. B. Back up the current transaction log. Restore the database to the point just before the data loss. C. Restore the database from the existing backup files to a time just before the data loss. D. Restore the database to the point of the last full backup.

A

A new employee needs access to a SQL Server 2005 database that is located on a server named SQL1. You create a login named ajones by using the following Transact-SQL statement. CREATE LOGIN ajones WITH PASSWORD = 'SQLServer$1' The new employee reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow the new employee to gain access to SQL1. What should you do? A. Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode. B. Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode. C. Ensure that the login name is created with square brackets ([]). D. Give the login access to a specific database by using the CREATE USER Transact-SQL

A

As a database developer, you need to create an application that will be downloaded from the Internet. The application requires a database to store application data. Which SQL Server edition can you use for your application that does not require application users to purchase a license for SQL Server 2005? A. SQL Server Express Edition B. SQL Server Workgroup Edition C. SQL Server Developer Edition D. SQL Server Standard Edition

A

How many default instances can you install on a single SQL Server server? A. 1 B. 2 C. 3 D. 4

A

The term in-place upgrade refers to which type of upgrade? A. Installing SQL Server 2005 in the same directory as the current installation and replacing the SQL Server files B. Installing a new instance of SQL Server 2005 and sharing the older databases between the new instance and the old instance C. Installing a new instance of SQL Server 2005 on the same server as the old instance and moving the databases from the old instance to the new instance D. Installing a new instance of SQL Server 2005 on a different server from the old instance and moving the databases from the old server to the new Server

A

You are creating a SQL Server 2005 application that will store and manage legal documents. The original documents are stored as XML documents on a file server. The application will be used to insert the documents into the database. Later, the documents must be retrieved from the database. These documents must be identical to the originals. You need to design a table to store the document data. What should you do? A. Store the XML in a column that has a data type of nvarchar(max). B. Shred the XML and store it in a relational structure. C. Store the XML in a column that has a data type of XML. D. Store the XML in a column that has a data type of varchar(8000).

A

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do? A. Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule. B. Create a job for each task that runs once a day. Use a Transact-SQL statement to check the date and day of the week. If the day is either a Sunday or the first day of the month, execute the code. C. Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Sunday or the first day of the month, execute the jobs. D. Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs the job on the first of the month.

A

You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance. You need to identify the most likely cause of the connection issues. What should you do first? A. Ensure that port 1433 is open in your firewall. B. Ensure that port 443 is open in your firewall. C. Ensure that client computers connect by using Shared Memory protocol. D. Ensure that the server is not paused.

A

You manage a database named DB1, which is located on a SQL Server 2005 computer. You receive a warning that the drive on which the DB1 log file is located is near capacity. Although the transaction log is backed up every five minutes, you observe that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in DB1. What should you do? A. Connect to the DB1 database. Execute DBCC OPENTRAN. View the SPID and Start time rows. B. Connect to the master database. Execute DBCC OPENTRAN. View the SPID and Start time rows. C. In SQL Server Management Studio, open the Activity Monitor. Select the Process Info page and apply the following filter settings. Database = DB1 Open Transactions = YesView the Process ID and Last Batch columns. D. Open a query window. Connect to the master database. Execute the following statement.SELECT TOP 1 spid, last_batch FROM sys.sysprocesses WHERE dbid = db_id('DB1') AND open_tran > 0 ORDER BY last_batch

A

You use an order application named App1, which connects to a SQL Server 2005 database named DB1. Because some actions take more than three seconds, users report slow performance when they use App1. You want to identify those actions by using the Tuning template in SQL Server Profiler to trace the application. You need to capture only those App1 actions in DB1 that have a response time of two seconds or more. What should you do? A. Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2000ApplicationNameLikeApp1DatabaseNameLikeDB1 B. Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2ApplicationNameLikeApp1DatabaseNameLikeDB1 C. Add the CPU column and create the following column filters.ColumnOperatorValueCPUGreater than or equal2000TextDataLikeApp1DatabaseNameLikeDB1 D. Add the ApplicationName and CPU columns and create the following column filters.ColumnOperatorValueCPUGreater than or equal2ApplicationNameLikeApp1TextDataLikeDB1 ***

A

You work in a company who prepares to install SQL Server 2005. There is one server and many clients. You need to select the protocols for the connection between the client and server when you install SQL Server 2005. Which two protocols could you choose in following? (One answer presents two completely solutions. Choose the correct one.) A. Named Pipes, TCP/IP B. TCP/IP, Multiprotocol C. Shared Memory, Virtual Interface Adapter (VIA) D. Virtual Interface Adapter (VIA), Named Pipes

A

Your SQL Server 2005 database contains a table that has 500 million rows of data. Some of the data is historical and some is current. You need to partition the data on a single server to increase performance and optimize maintenance. What should you do? A. Implement horizontal partitioning. B. Implement vertical partitioning. C. Implement a raw partition. D. Implement distributed partitioning.

A

You are preparing for a new installation of SQL Server 2005. You need to select the protocols that client computers might use to connect to the server. Which two protocols can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. Named Pipes B. TCP/IP C. Shared Memory D. Virtual Interface Adapter (VIA) E. Multiprotocol

A,B

You manage a SQL Server 2005 computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.) A. In Event Viewer, view the system log. B. In Event Viewer, view the application log. C. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file. D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file. E. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.

A,B,D

Company.com uses SQL Server 2005. A user reports that an order processing application stopped responding in the middle of an order transaction. The users SQL Server session ID is 54. You need to find out if session 54 is blocked by another connection. If it is, you need to identify the blocking session ID. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. In SQL Server Management Studio, open the Activity Monitor. Open the Process Info page. View the BlockedBy column for session 54. B. In SQL Server Management Studio, open the Activity Monitor. Open the Locks by Process page. View the Request Mode column for session 54. C. In SQL Server Management Studio, open a new query window and execute the following statement.SELECT * FROM sys.dm_exec_requests WHERE session_id = 54View the blocking_session_id column. D. In SQL Server Management Studio, open a new query window and execute the following statement.SELECT * FROM sys.dm_exec_sessionsWHERE session_id = 54View the status column.

A,C

Company.com uses a SQL Server 2005 database. This database contains a trigger named trg_InsertOrders, which fires when order data is inserted into the Orders table. The trigger is responsible for ensuring that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort. Which two Transact-SQL statements can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. ALTER TABLE OrdersDISABLE TRIGGER trg_InsertOrders B. DROP TRIGGER trg_InsertOrders C. DISABLE TRIGGER trg_InsertOrders ON Orders D. ALTER TRIGGER trg_InsertOrders ON Orders NOT FOR REPLICATION E. sp_settriggerorder@triggername= 'trg_InsertOrders', @order='None'

A,C

Which SQL Server 2005 services require that you install them with their own account? (Choose all that apply.) A. SQL Server Agent B. Log Reader Agent C. SQL Server D. Replication Agent

A,C

You are writing a query that returns data from tables in a SQL Server 2005 database. Some of the data is stored in relational columns, and some of the data is stored in XML data type columns. Your query needs to return a relational result set that contains data from relational fields and attribute values from XML data type columns. Which two methods of the XML data type should you use? (Each correct answer presents part of the solution. Choose two.) A. The value() method B. The exist() method C. The query() method D. The nodes() method E. The modify() method

A,D

You use a SQL Server 2005 computer that was installed with the default settings. A database named DB1 is located on the server. Due to the recent failure of a hard disk controller, you decide to check the integrity of DB1. You create a maintenance task named CheckDB1 that uses default settings. Because this check must not interfere with user transactions, you schedule it to run over the weekend. The following Monday, you need to retrieve the outcome details of the CheckDB1 maintenance plan. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. In SQL Management Studio, right-click the CheckDB1 maintenance plan. On the context menu, click View History. See the record for the plan named CheckDB1. B. In SQL Management Studio, right-click the Activity Monitor. On the context menu, click View Processes. Look for a record that has "CheckDB1" in the Application column. C. In Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. View the SQLAgent.out text file. D. In Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. View the text file that has a name that starts with "CheckDB1_" followed by the execution year, date, and time.

A,D

You work for a bank that uses a SQL Server 2005 database to store line items from customer banking transactions. The bank processes 50,000 transactions every day. The application requires a clustered index on the TransactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date. What are the two ways to achieve this goal? (Each correct answer presents a completesolution. Choose two.) A. Place a nonclustered index on the date column. B. Add a unique clustered index on the date column. C. Map each partition to a filegroup, with each filegroup accessing a different physical drive. D. Create a partitioning scheme that partitions the data by date.

A,D

Company.com has a network that consists of two domains named DOM1 and DOM2. There is no trust relationship between DOM1 and DOM2. DOM1 contains a server named SQL1. DOM2 contains a server named SQL2. Both servers run SQL Server 2005. The SQL Server instance on SQL1 uses the DOM1\SQLService1 service account. The SQL Server instance on SQL2 uses the DOM2\SQLService2 service account. There are no mirroring endpoints on either server. SQL1 contains a database named DB1. You want to mirror DB1 to SQL2 by using high-protection mode without a witness. You need to create the database mirroring endpoints and configure security on all participating server instances. What should you do? A. Create endpoints and configure security by using the Configure Database Mirroring Security Wizard for DB1. Set up the endpoints to use SQL Server Authentication. B. Configure database mirroring endpoints and security for each server instance by using Transact-SQL. Allow database mirroring to use encrypted certificates for outbound and inbound connections. C. Create logins and add them to the sysadmin group. Configure the logins to use SQL Server Authentication on each of the server instances. Create endpoints by using the Configure Database Mirroring Security Wizard on the Database Properties Mirroring page for DB1. D. Connect to each server instance and execute the following statement.CREATE ENDPOINT mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING ( AUTHENTICATION = WINDOWS NTLM, ROLE=PARTNER);

B

Company.com has two SQL Server 2005 computers named SQL1 and SQL2. A user in Company.com named Eric writes many ad hoc queries against the company databases. Eric has access to the Customer database on SQL1. He does not have access to the Sales database on SQL2. You need to ensure that Eric can write queries that join information from both servers. What should you do first? A. Create a linked server on SQL1 to SQL2. Configure the linked server to use impersonation. B. Create a linked server on SQL1 to SQL2. Configure the linked server to use mapped logins. C. Instruct Eric to write the queries on SQL2 by using the OPENQUERY statement and specifying SQL1 as the server name. D. Instruct Eric to specify SQL Server object names by using four-part notation.

B

Company.com is storing its customer data in a data warehouse.It includes three separate SQL Server 2005 tables for storing customer data. Each table is used to horizontally partition data that is migrated from a SQL Server 2000 installation to SQL Server 2005. Data is partitioned as shown in the table displayed in the exhibit. The database does not allow customers to opt-out of e-mail marketing offers. You need to add a new OptOutFlag column to each of the customer tables. You must ensure that this column is added to either all three customer tables or none of them. You must also ensure that the database is always in a consistent state. Which Transact- SQL batch should you use? A. BEGIN TRAN ALTER TABLE CUSTOMER1 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER2 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3 ADD OptOutFlag BIT NULL IF @@error <> 0ROLLBACK TRANELSECOMMIT TRAN B. BEGIN TRAN BEGIN TRY ALTER TABLE CUSTOMER1 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER2 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3 ADD OptOutFlag BIT NULL END TRY BEGIN CATCH ROLLBACK TRAN RETURN END CATCH COMMIT TRAN C. ALTER TABLE CUSTOMER1ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER2ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3ADD OptOutFlag BIT NULL D. BEGIN TRAN ALTER TABLE CUSTOMER1 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER2 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3 ADD OptOutFlag BIT NULL COMMIT TRAN

B

Company.com uses a SQL Server 2005 database. Customer data from your trading partners is imported into the Customers table every night. You need to ensure that the customer record is updated if it already exists in the Customers table. If the record does not exist, the data needs to be inserted into the Customers table. What should you do? A. Create a FOR trigger. B. Create an INSTEAD OF trigger. C. Create an AFTER trigger. D. Create a DDL trigger.

B

Kart works in a company which uses a SQL Server 2005 database. This company gives customers some financial reports. There are some views are used in ad hoc queries and they can enforce this company??s policy of abstracting data. Kart finds that some of these views perform slowly. So he wants to increase performance and maintain the security policy at the same time. He creates indexes on those views. One of the views returns the current date as one of the columns by use of the GETDATE() function, but you are prohibited to create a index on this view. What should you do? A. Remove all deterministic function calls from within the view. Schema-bind all functions that are called from within the view. B. Remove all nondeterministic function calls from within the view. Schema-bind all functions that are called from within the view. C. Remove all nondeterministic function calls from within the view. Create the view and specify the WITH CHECK OPTION clause. D. Remove all deterministic function calls from within the view. Create the view and specify the WITH CHECK OPTION clause.

B

Mike works in a website. This website uses a SQL SERVER 2005 database. There is a trigger named trg_IO in this database. This trigger fires when you insert order data into Order table to ensure that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger with the least amount of administrative effort and prevent it from firing during the data import process at the same time. What should you do? A. DROP TRIGGER trg_IO B. DISABLE TRIGGER trg_IO ON Orders C. ALTER TRIGGER trg_IO ON Orders NOT FOR REPLICATION D. sp_settriggerorder@triggername= 'trg_IO', @order='None'

B

You are a database developer for a company that manufactures bicycles. You are creating a Web application that will retrieve data from a database named AdventureWorks. The application connects to a database server that is running SQL Server 2005. One of the functions of your application will allow shift supervisors to see how many bicycles were manufactured during the current shift. A user named Tom creates a stored procedure named usp_GetPersonData in the dbo schema. You are creating an HTTP endpoint that exposes a Web method named PersonData. This Web method allows client applications to access the stored procedure. You use the following statement to create the endpoint. CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH ='/AWpersons', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'SQL1') FOR SOAP (WEBMETHOD '' (NAME=''), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', NAMESPACE = 'http://Adventure-Works/Persons') You need to specify the correct WEBMETHOD and NAME to create the endpoint. Which command should you use? A. WEBMETHOD 'http://www.adventure-works.com /PersonData' NAME = 'AdventureWorks.dbo.usp_GetPersonData' B. WEBMETHOD 'PersonData'NAME = 'AdventureWorks.dbo.usp_GetPersonData' C. WEBMETHOD 'PersonData'NAME = 'AdventureWorks.Tom.usp_GetPersonData' D. WEBMETHOD 'AdventureWorks.PersonData'NAME = 'AdventureWorks.Tom.usp_GetPersonData'

B

You are creating an online catalog application that will display product information on the company Web site. The product data is stored in a SQL Server 2005 database. The data is stored as relational data but must be passed to the application as an XML document by using FOR XML. You test your application and notice that not all of the items matching your query appear in the XML document. Only those products that have values for all elements in the schema appear. You need to modify your Transact-SQL statement so that all products matching your query appear in the XML document. What should you do? A. Add an XML index to the table that contains the product data. B. Add the XSINIL argument to the ELEMENTS directive in the query. C. Add a HAVING clause to the query. D. Add the replace value of clause to the query.

B

You are modifying a table named Product in a SQL Server 2005 database. You want to add a new column named FriendlyName to the Product table. A friendly name for each product will be stored in this column. The table currently contains data. The sales department has not yet created a friendly name for each product. FriendlyName is a required value for each product. You want to add this new column by using the least amount of effort. What should you do? A. Define the new column as NULL. Update the FriendlyName column to the same value as the ProductName column. Alter the FriendlyName column to be NOT NULL. B. Define the new column as NOT NULL with a default value of 'Undefined.' C. Define the new column as NULL. Use application logic to enforce the data constraint. D. Define the new column as NULL with a default value of 'Undefined.'

B

You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet database is used by the TimeEntry application. You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. What should you do? A. Move the database to the new server by using the SQL Management Object method in the Copy Database Wizard. B. Detach the current database. Copy the data files to the new server. Attach the files. C. Back up the database. Copy the backup file to the new server. Restore the database. D. Move the data files. Specify the new location by using ALTER DATABASE.

B

You are responsible for configuring and maintaining the backup jobs for a SQL Server 2005 computer. Backup files are written to a tape drive that is connected to the database server. In the future, your department will install a new backup server. Then backup files will be written to disk on the new server. To simplify maintenance, you need to configure the backup jobs so that minimal changes will be necessary to back up to the new server. What should you do? A. Configure the backup jobs to write to an internal disk. When the new backup server is available, alter the backup jobs. B. Configure the backup jobs to write to backup devices that reference the tape drive. Alter the backup devices to point to the new backup server after the new server is installed. C. Configure the backup jobs to write directly to the tape drive. Move the tape drive to the new backup server. D. Configure the backup jobs to write to an internal disk. Alter the backup jobs to use backup devices after the new server is in place.

B

You are responsible for importing data into SQL Server 2005 databases. Your department is starting to receive text files that contain sales transactions from stores across the country. Columns in the data are separated by semicolons. You need to import the files into the sales database. What should you do? A. Create a custom format file, specifying a semicolon as the row terminator. B. Use the bcp command, specifying a semicolon as the field terminator. C. Use the bcp command with the default arguments. D. Use the BULK INSERT statement with the default arguments.

B

You are responsible for maintaining a SQL Server 2005 database. Business analysts in the company routinely use a view named v_CustomerSales to join the Customers and Sales tables in the database. They use the view to aggregate total sales by customer by month. You need to increase the performance of the view. What should you do? A. Update the view to use an outer join between the Customers and Sales tables. B. Create a clustered index on the v_CustomerSales view. C. Create two separate views that do not contain any joinsone view named v_Customers for the Customers table and another one named v_Sales for the Sales table. D. Create a stored procedure for the business analysts that uses the v_CustomerSales view

B

You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file. You need to locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak. Which Transact-SQL statement should you use? A. RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak B. RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak C. RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak D. RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak

B

You discover that the schema changes that were recently made to your SQL Server 2005 database have caused your Web site to stop functioning. It is unclear who made the changes. Company.com now mandates that all changes to the database schema be tracked. You need to implement a mechanism that will track schema changes in your database. What should you do? A. Implement a stored procedure that writes data about schema changes to a log table. B. Implement DDL AFTER triggers that write user and schema information to a log table. C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table. D. Implement a DML AFTER trigger that writes data about schema changes to a log table.

B

You use Service Broker to manage data requests between SQL Server 2005 databases. You need to make changes to objects for an existing service. You want to ensure that no messages can be received by the service until the changes are complete. What should you do? A. Create the queue with ACTIVATION STATUS OFF. Set it to ON when the service is ready. B. Create the queue with STATUS OFF. Set it to ON when the service is ready. C. Create the queue with only the queue_name argument. Set the other parameters when the service is ready. D. Create the queue with max_queue_readers = 0. Set it to an optimum value when the service is ready.

B

You use a SQL Server 2005 database named DB1, which is located on a server named SQL1. DB1 is in use 24 hours a day, 7 days a week. A recent copy of DB1 exists on a second server named SQLtest that also runs SQL Server 2005. You detect a high number of full scans on SQL1 and conclude that additional indexes in DB1 are needed. A workload file that is suitable for Database Engine Tuning Advisor (DTA) already exists. You need to analyze the workload file by using DTA. You must ensure maximum performance on SQL1 during analysis. You must also ensure availability during the implementation of any recommendations suggested by the DTA. What should you do? A. Store the workload file on SQL1. Start DTA on SQLtest and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box. B. Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box. C. Store the workload file on SQL1. Start DTA on SQL1 and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box. D. Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box.

B

Company.com uses SQL Server 2005. You are implementing a series of views that are used in ad hoc queries. The views are used to enforce Company.coms security policy of abstracting data. Some of these views perform slowly. You create indexes on those views to increase performance, while still maintaining the companys security policy. One of the views returns the current date as one of the columns. The view returns the current date by using the GETDATE() function. This view does not allow you to create an index. You need to create an index on the view. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) A. Remove all deterministic function calls from within the view. B. Remove all nondeterministic function calls from within the view C. Schema-bind all functions that are called from within the view. D. Create the view and specify the WITH CHECK OPTION clause.

B,C

You use a SQL Server 2005 database that contains a table named Customers. The Customers table contains the columns displayed in the exhibit. You create a view named v_Customer by using the following Transact-SQL statement. CREATE VIEW v_CustomerASSELECT CustomerID, CompanyName, FirstName, LastName, PictureFROM Customers You want to create an index on the v_Customer view. Prior to creating the index, you need to modify the Transact-SQL statement. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) A. Change the Picture column to an image data type. B. Include the schema name with the table name. C. Include the WITH SCHEMABINDING clause. D. Select all columns by using the * notation

B,C

Company.com uses a mission-critical database named DB1, which is located on a server named SQL1. You want to mirror DB1 on a second server named SQL2. Before you can establish database mirroring, you need to prepare DB1 and the servers for mirroring. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.) A. Set the recovery model of DB1 on SQL1 to bulk-logged recovery. B. Set the recovery model of DB1 on SQL1 to full recovery. C. Back up DB1 on SQL1. Restore the backup on SQL2; specify the NORECOVERY option. D. Back up DB1 on SQL1. Restore the backup on SQL2; specify the STANDBY option. E. Back up DB1 on SQL1. Restore the backup on SQL2; specify the RECOVERY option. F. Create endpoints on all participating servers. G. Set the AutoShrink property of DB1 on SQL1 to false.

B,C,F

Company.com has a server named SQL1 that is dedicated to SQL Server 2005. SQL1 is configured for SQL Server and Windows Authentication mode. Using Windows Authentication, you have sysadmin privileges on SQL1. All users who work with SQL1 report that all applications that use SQL1 stop responding. Your supervisor asks you to investigate the problem. In Task Manager, you notice that CPU usage is at 100 percent. You try to connect to SQL1 by using SQL Server Management Studio. SQL1 does not respond. You need to connect to SQL1 to diagnose the problem. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. Connect to SQL1 by using the osql command-line utility. B. Connect to SQL1 by using the sqlcmd command-line utility using a dedicated administrator connection. C. Connect to SQL1 by using SQL Server Management Studio. Open a query window and execute the following command.DBCC TRACEON 1224 D. In SQL Server Management Studio, on the toolbar, click Database Engine Query to open the Connect to Database dialog box. In the Server name text box, type ADMIN:SQL1. Select SQL Server Authentication and provide your sa credentials. Click the Connect button.

B,D

Company.com uses SQL Server 2005. A user reports that an order processing application stopped responding during a transaction. You investigate and discover that the user's session, which is session ID 85, is being blocked by an errant process. The session ID of the errant process is 101. You need to solve this blocking situation quickly so that the user can continue using the application without losing any work. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. In SQL Server Management Studio, open a new query window. Execute the following statement.KILL 85 B. In SQL Server Management Studio, open a new query window. Execute the following statement.KILL 101 C. In SQL Server Management Studio, open the Activity Monitor. Open the Process Info page. Right-click session ID 85 and select Kill Process. D. In SQL Server Management Studio, open the Activity Monitor. Open the Process Info page. Double-click session ID 101 to show the process details. Click the Kill Process button. E. In SQL Server Configuration Manager, stop and then restart the SQL Server (MSSQLSERVER) service. F. At a Microsoft Windows command prompt, execute the following statements.NET STOP MSSQLSERVERNET START MSSQLSERVER

B,D

You are implementing transaction log shipping for a database named DB1 from a server named SQL1 to a server named SQL2. Because DB1 is 100 GB in size, it is too big to transfer over the network in a reasonable amount of time. You need to minimize the impact on the network while you initialize the secondary database. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) A. Specify the simple recovery model for DB1. B. Specify either the full or the bulk-logged recovery model for DB1. C. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the RECOVERY option. D. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the STANDBY option. E. Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server.BACKUP LOG DB1 WITH TRUNCATE_ONLY

B,D

**Company.com has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named Table1. Table1 is replicated to a database named DB1Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against Table1 in DB1 Repl are very slow. You investigate and discover that only the clustered index of Table1 is replicated. All other indexes in DB1Repl are missing. You examine the Table1 article properties. The current Table1 article properties are shown in the exhibit. You need to change the article properties so that all indexes of Table1 in DB1 are replicated when the subscription is reinitialized. Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.) A. Copy clustered index B. Copy nonclustered indexes C. Copy extended properties D. Copy unique key constraints E. Copy index partitioning schemes F. Copy XML indexes

B,F

**Company.com has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named Table1. Table1 is replicated to a database named DB1Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against Table1 in DB1Repl are very slow. You investigate and discover that only the clustered index of Table1 is replicated. All other indexes in DB1Repl are missing. You examine the Table1 article properties. The current Table1 article properties are shown in the exhibit. You need to change the article properties so that all indexes of Table1 in DB1 are replicated when the subscription is reinitialized. Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.) A. Copy clustered index B. Copy nonclustered indexes C. Copy extended properties D. Copy unique key constraints E. Copy index partitioning schemes F. Copy XML indexes

B,F

A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do? A. Execute DBCC CHECKFILEGROUP for each filegroup. B. Execute DBCC CHECKCATALOG. C. Execute DBCC CHECKDB. D. Execute DBCC CHECKTABLE for each table.

C

A website has a SQL Server 2005 database, and Kenny is a supporter of this website. The execution of the database is slow. Kenny discovers that some optimizer statistics are missing and others are out of date. Now, Kenny would take some measures to make the reports execute more quickly. Which two Transact-SQL statements would he use? A. DBCC CHECKTABLE and ALTER INDEX REORGANIZE B. ALTER INDEX REORGANIZE and DBCC UPDATEUSAGE C. UPDATE STATISTICS and CREATE STATISTICS D. DBCC SHOW_STATISTICS and DBCC UPDATEUSAGE

C

Application developers in Company.com create an assembly that contains a CLR function. This CLR function reads data from a spreadsheet, performs some calculations, and returns the data to a SQL Server 2005 computer. You need to register the assembly with SQL Server 2005 by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use? A. Default B. SAFE C. EXTERNAL_ACCESS D. UNSAFE

C

BooksSales is a company which major in books sale. Benny is a DBA of this company. They use a SQL Server 2005 database. Benny wants to create a view to join the Books and Customers. Benny needs to ensure that the view cannot be affected by modifications to underlying table schemas by use of the least possible amount of overhead. What action would Benny perform? A. Specifying the WITH CHECK option and create the view. B. On the tables, create CHECK constraints. C. Create the view, specifying the WITH SCHEMABINDING option. D. Set up a DDL trigger to roll back any changes to the tables if the changes affect the columns in the view.

C

Company.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do? A. Ensure that both SQL1 and SQL2 use the same login name as the security context for each server. B. Configure SQL2 as a remote server. Write the query on SQL1. C. Configure SQL2 as a linked server to impersonate the remote login. D. Configure SQL2 as a distributed server. Use pass-through authentication

C

Company.com has two SQL Server 2005 computers named SQL1 and SQL2. Both servers take part in replication. SQL1 is both the Publisher and its own Distributor of a publication named Pub1. Pub1 is the only publication on SQL1, and SQL2 is the only Subscriber. Your supervisor requests a status report about the replication latencies. Using Replication Monitor on SQL1, you need to find out the current latencies between the Publisher and Distributor as well as between the Distributor and Subscriber. What should you do? A. Select the Subscription Watch List tab for SQL1. View the Latency column for the SQL2 subscription. B. Select the All Subscriptions tab for the Pub1 publication. View the Latency column for the SQL2 subscription. C. Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the requested latency values for the SQL2 subscription to appear. D. Select the Subscription Watch List tab for SQL1. Double-click the SQL2 subscription. View the duration details on the Publisher to Distributor History tab as well as on the Distributor to Subscriber History tab.

C

During the design process for a new production installation of SQL Server 2005, you determine that you need to partition the database. Which edition supports data partitioning? A. SQL Server Express Edition B. SQL Server Workgroup Edition C. SQL Server Enterprise Edition D. SQL Server Standard Edition

C

Kart is a DBA of a company. This company has a website. Buyers can send their requests on this company website. Kart stores these requests in a table by use of a SQL Server 2005 database. He names this table REQUEST. Now Kart needs to implement full-text searching in order run reports on the comments. What would he do? (Each correct answer presents part of the solution. Choose two.) A. Create a nonclustered index on the Comments column. Execute the USE Master Transact- SQL statement B. Create a full-text catalog. Execute the USE Master Transact-SQL statement. C. Create a full-text catalog. Create a full-text index on the Comments column. D. Create a nonclustered index on the Comments column. Create a full-text index on the Comments column.

C

Which authentication mode lets you use both SQL Server logins and Windows logins? A. Kerebos authentication B. Windows C. Mixed Mode D. Network Service account

C

Which of the following statements can you use to create a filegroup? A. ALTER DATABASE ADD FIL B. ALTER DATABASE MODIFY FILEGROU C. ALTER DATABASE ADD FILEGROU D. ALTER DATABASE REMOVE FILEGROU

C

You are a DBA at a SQL Server hosting organization. You need to ensure that each of your organizations client instllations can operate with different service packs of SQL Server. What can you do to achieve your goals without requiring a different server for each client? (Choose the answer that reflects best practices.) A. Create one SQL Server installation and create a different database for each client. B. You cannot support different service packs on one server. C. Install a different instance of SQL Server for each client on your servers. D. Place clients together on servers that have SQL Server installations that have the required service packs

C

You are creating a SQL Server 2005 database for a mortgage company. The database will support a new Web-based application that will handle up to 1,000 simultaneous users. This application must quickly display the results of calculation-intensive operations, such as calculating mortgage payments and amortization schedules. You need to ensure that the database processes calculations as quickly and efficiently as possible. What should you do? A. Implement parameterized Transact-SQL queries in the application. B. Implement Transact-SQL stored procedures in the database. C. Implement CLR stored procedures in the database. D. Implement distributed Web services.

C

You are creating a Web-based application to manage data aggregation for reports. The application connects to a SQL Server 2005 database named DataManager. One page in the application has controls that execute stored procedures in a database named ReportingDatabase. There is an existing Service Broker connection between the DataManager database and ReportingDatabase. You want to add two new message types to the existing service. In each database, you create message types named ProcessReport and SendResult. You need to add the two new message types to the existing service. What should you do first? A. Create a queue on each database with the ACTIVATION argument set to DataManager.dbo.ProcessReport. B. Create a conversation between the databases by using the following statement.BEGIN DIALOG FROM SERVICE 'ProcessReport' TO SERVICE 'SendResult' C. Create a contract between the services by using the following statement.CREATE CONTRACT ProcessData (ProcessReport SENT BY INITIATOR, SendResult SENT BY TARGET) D. Create services for each database by using the following statement.CREATE SERVICE DataManager ON QUEUE ProcessReport

C

You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur. BEGIN TRY BEGIN TRANSACTION DELETE FROM Person.Contact WHERE ContactID = @ContactID COMMIT TRANSACTIONEND TRYBEGIN CATCH DECLARE @ErrorMessage nvarchar(2000) DECLARE @ErrorSeverity int DECLARE @ErrorState int SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do? A. Add a COMMIT TRANSACTION command to the CATCH block. B. Remove the COMMIT TRANSACTION command from the TRY block. C. Add a ROLLBACK TRANSACTION command to the CATCH block. D. Add a ROLLBACK TRANSACTION command to the TRY block.

C

You are creating a view to join the Customers and Orders tables in a SQL Server 2005 database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do? A. Create CHECK constraints on the tables. B. Create a DDL trigger to roll back any changes to the tables if the changes affect the columns in the view. C. Create the view, specifying the WITH SCHEMABINDING option. D. Create the view, specifying the WITH CHECK option

C

You are creating a view to join the Customers and Orders tables in a SQL Server 2005 database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do? A. Create CHECK constraints on the tables. B. Create a DDL trigger to roll back any changes to the tables if the changes affect the columns in the view. C. Create the view, specifying the WITH SCHEMABINDING option. D. Create the view, specifying the WITH CHECK option.

C

You are creating an HTTP endpoint that will be used to provide customer data to external applications. Your SQL Server 2005 computer is named SQL1. You create a stored procedure named dbo.usp_GetPersonData to retrieve the data in the AdventureWorks database. You create the endpoint by using the following code. CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH = '/AWpersons', AUTHENTICATION =(INTEGRATED), PORTS = (CLEAR), SITE = 'SQL1') FOR SOAP (WEBMETHOD 'PersonData' (NAME='AdventureWorks.dbo.usp_GetPersonData'), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', NAMESPACE = 'http://Adventure-Works/Persons') The first users to connect to the endpoint tell you that they do not get any data. You connect to the endpoint and discover that it is not responding. You need to modify the endpoint so that data is returned as expected. What should you do? A. Change the AUTHENTICATION property to KERBEROS. B. Specify BATCHES = ENABLED. C. Specify STATE = Started. D. Specify WSDL = 'pr_GetPersonData'.

C

You are in charge of designing the physical structure for your companysv new server running SQL Server 2005. The server has the following characteristics: two disks in RAID 1, five disks in RAID 5, and another ten disks in RAID5. Where should you store database files for the best performance? A. Use RAID 1 to install the operating system. Use the first RAID 5 disk set to install SQL Server executable files and the second RAID 5 disk set to store database files. B. Use RAID 1 to install the operating system. Use the first RAID 5 system to install SQL Server executable files and data and transaction log files. Use the second RAID 5 system to store database backups. C. Use RAID 1 to install the operating system and SQL Server executable files. Use the first RAID 5 system to store transaction log files. Use the second RAID 5 system to store data files. D. Use the first RAID 5 system to install the operating system and SQL Server executable files. Store data files in the second RAID 5 system and log files in the RAID 1 system. from a database.

C

You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process, each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database. You define a foreign key between these two tables. Thus, referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do? A. Drop the foreign key. Import the data by using the script. Re-create the foreign key. B. Create a CHECK constraint. C. Create a DML INSTEAD OF trigger that writes the failed records to a file or table. D. Create a DML AFTER trigger that writes the failed records to a file or table.

C

You are responsible for maintaining scheduled jobs for the SQL Server 2005 databases in your department. One of the jobs aggregates data from multiple sources for reports. This job runs daily. The job consists of multiple steps. Each step aggregates data for a specific report. A user reports that the data for some of the reports has not been updated recently. You need to ensure that every step of the job that aggregates data executes even when errors occur. What should you do? A. Combine all of the steps into a single step that runs once a day. B. Create a notification that alerts you when an error occurs so that you can correct the error and restart the job. C. Change the On Failure action to go to the next step. D. Configure the job to retry the step.

C

You are responsible for managing a SQL Server 2005 database that stores sales information. Many values in nchar columns in the database tables contain preceding or trailing spaces. You need to implement a mechanism that selects the data from the tables without leading and trailing spaces. Your solution must be available for reuse in Transact-SQL statements and views. What should you do? A. Create DML triggers that query the inserted and deleted tables. B. Create a stored procedure that calls the LTRIM and RTRIM built-in functions. C. Create a Transact-SQL function that calls the LTRIM and RTRIM built-in functions. D. Call the TRIM built-in function.

C

You are responsible for managing a SQL Server 2005 database. This database is updated with data that is exported from another site. On a monthly basis, data is loaded into the database from a flat file by using the bcp utility. This operation should be minimally logged. However, users report that they receive error messages indicating that the transaction log is full. You investigate and discover that the errors occur when the data is being imported. You need to prevent this problem from occurring. What should you do? A. Disable the clustered index and all nonclustered indexes on the target table. B. Set the database recovery model to full. C. Set the database recovery model to bulk-logged. D. Use the BULK INSERT statement to load the data.

C

You discover that all indexes of the Sales table on your SQL Server 2005 database server are heavily fragmented. You need to decrease the fragmentation of all indexes of the Sales table to a minimum, while keeping the Sales table available to all users. What should you do? A. Defragment the disk that contains the Sales table. B. Execute the following statement.USE master;GODBCC CLEANTABLE ('DB1','sales');GO C. Execute the following statement.USE DB1;GOALTER INDEX ALL ON sales REORGANIZE WITH (LOB_COMPACTION = ON);GO D. Execute the following statement.USE DB1;GOALTER INDEX ALL ON sales REBUILD;GO

C

You manage a SQL Server 2005 database that contains a table with many indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use? A. sys.dm_fts_index_population B. sys.dm_exec_query_stats C. sys.dm_db_index_usage_stats D. sys.dm_db_index_physical_stats

C

You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns. You need to satisfy these requirements by using the least amount of effort. What should you do? A. Specify StateProvince, Surname, and FirstName in the ORDER hint in the bcp out command. B. Create a format file for the export operation. C. Specify StateProvince, Surname, and FirstName in the ORDER BY clause in the bcp queryout command. D. Copy the data into a new table that has a clustered index on StateProvince, Surname, and FirstName. Export the data.

C

You work for a company that sells books. You are creating a report for a SQL Server 2005 database. The report will list sales representatives and their total sales for the current month. The report must include only those sales representatives who met their sales quota for the current month. The monthly sales quota is $2,000. The date parameters are passed in variables named @FromDate and @ToDate. You need to create the report so that it meets these requirements. Which SQL query should you use? A. SELECT s.AgentName, SUM(ISNULL(o.OrderTotal,0.00))AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY s.AgentName B. SELECT s.AgentName, SUM(ISNULL (o.OrderTotal,0.00))AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.OrderDate BETWEEN @FromDate AND @ToDate AND o.OrderTotal >= 2000GROUP BY s.AgentName C. SELECT s.AgentName, SUM(ISNULL (o.OrderTotal,0.00)) AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentID WHERE o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY s.AgentNameHAVING SUM(o.OrderTotal) >= 2000 D. SELECT s.AgentName, SUM(ISNULL(o.OrderTotal,0.00)) AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.ordertotal = 2000 AND o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY s.AgentNameHAVING SUM(o.OrderTotal) >= 2000

C

You work in a company which uses SQL Server 2005. Your company prepares to develop an application. Application developers create an assembly. This assembly contains a CLR function that can read data from a spreadsheet, performs some calculations, and returns the data to a SQL Server 2005 computer. You need to grant the least privileged security to the assembly when you register it with SQL Server 2005 by using the CREATE ASSEMBLY statement. Which permission set should you use? A. UNSAFE B. Default C. EXTERNAL_ACCESS D. SAFE

C

Your database named DB1 is one of 15 databases located on a SQL Server 2005 computer named SQL1. Recently, users who work with DB1 report that they experience long query execution times. You want to reproduce and analyze this problem in a test environment that holds all copies of the production databases. You need to record the workload for an entire day on SQL1 for later execution on the test server. You must ensure that all events are captured without skipping any, even if SQL1 is under stress. What should you do? A. Start SQL Server Profiler on SQL1 and connect locally. Use the Tuning template. Create a filter for the DatabaseID column that is equal to the database ID of DB1. Save the trace to a table on SQL1. B. Start SQL Server Profiler on your computer and connect to SQL1. Use the Tuning template. Save the trace to a table on the test computer. Ensure that the Server processes trace data option is selected. C. Start SQL Server Profiler on the test computer and connect to SQL1. Use the Replay template. Save the trace to a file on the test computer. Ensure that the Server processes trace data option is selected. D. Start SQL Server Profiler on your computer and connect to SQL1. Use the Replay template. Save the trace to a file on your computer. Ensure that the Server processes trace data option is not selected.

C

Company.com Web site includes a page that customers use to send feedback about the company and its products. You use a SQL Server 2005 database to store the comments in the Comments column of a table named Feedback. You need to implement full-text searching so that you can run reports on the comments. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) A. Create a nonclustered index on the Comments column. B. Execute the USE Master Transact-SQL statement. C. Create a full-text catalog. D. Create a full-text index on the Comments column.

C,D

Company.com has a SQL Server 2005 computer. You have been assigned the task of retrieving information about a user who is currently logged in. You need to create a function that returns scalar information about the activity time for a particular user. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.) A. Create a function that returns a list of values that represent the login times for the given user. B. Create a function that returns a list of values that represent the people who have logged more hours than the current user has logged. C. Create a function that returns a numeric value that represents the number of hours that a user has logged for the current day. D. Create a function that returns a numeric value that represents the number of hours that a user has logged for the current month.

C,D

Company.com uses SQL Server 2005. Users report that report execution is slow. You investigate and discover that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing and others are out of date. You need to correct the problem so that reports execute more quickly. Which two Transact-SQL statements should you use? (Each correct answer presents part of the solution. Choose two.) A. DBCC CHECKTABLE B. ALTER INDEX REORGANIZE C. UPDATE STATISTICS D. CREATE STATISTICS E. DBCC SHOW_STATISTICS F. DBCC UPDATEUSAGE

C,D

A full backup of your database named DB1 is created automatically at midnight every day. Differential backups of DB1 occur twice each day at 10:00 and at 16:00. A database snapshot is created every day at noon. A developer reports that he accidentally dropped the Pricelist table in DB1 at 12:30. The last update to Pricelist occurred one week ago. You need to recover the Pricelist table. You want to achieve this goal by using the minimum amount of administrative effort. You must also minimize the amount of data that is lost. What should you do? A. Restore the most recent backup into a new database named DB1bak. Apply the most recent differential backup. Copy the Pricelist table from DB1bak to DB1. B. Delete all database snapshots except the most recent one. Restore DB1 from the most recent database snapshot. C. Recover DB1 from the most recent backup. Apply the most recent differential backup. D. Copy the Pricelist table from the most recent database snapshot into DB1.

D

A website provides stock information to users. This website uses a SQL Server 2005 database. A Web service provided by a third party is used to gain the real-time investment information. Users can see stock reports that combine data and this data are retrieved from the database with this real-time investment information. You need to ensure the information is current when the database executes the reports. What should you do when you create the appropriate objects that support the reports? A. Create a table to store the investment information for each customer. Create a trigger that fires when data is inserted into the table that joins with the data coming from the Web service. B. Publish the data in the database as an XML Web service by using the FOR XML AUTO clause. C. Create a Transact-SQL stored procedure that uses a temporary table to store the investment information for each customer. Update the table with the values from the Web service. D. Create a CLR function by using the assembly that calls the remote Web service. Call the CLR function and combine the results with investment information in the database.

D

Company.com has a server named SQL1 that runs SQL Server 2005 Enterprise Edition. SQL1 has 2 GB of RAM, 1.6 GB of which are used by the default SQL Server database engine instance. The average data growth of all databases combined is 100 MB a month. Users state that report execution times are increasing. You want to assess whether more RAM is needed. You need to use System Monitor to create a counter log that will help you decide whether to add RAM. Which performance object should you add to the counter log? A. MSAS 2005:Cache B. MSAS 2005:Memory C. MSAS 2005:Proactive Caching D. SQLServer:Buffer Manager E. SQLServer:SQL Statistics F. SQLServer:General Statistics

D

Company.com has two SQL Server 2005 computers named SQL1 and SQL2. Transaction log shipping occurs from SQL1 to SQL2 by using default SQL Server Agent schedule settings. You need to reconfigure transaction log shipping to provide minimum latency on SQL2. What should you do? A. On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings for both the log shipping copy and the restore jobs. B. On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts. C. On SQL1, maintain default schedule settings for the transaction log backup job. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts. D. On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.

D

Company.com uses SQL Server 2005. Users report with increasing frequency that they receive deadlock error messages in an order processing application. You need to monitor which objects and SQL Server session IDs are involved when deadlock conditions occur. You want information about each participant in the deadlock. What should you do? A. Trace the Lock:Timeout event by using SQL Server Profiler. B. Observe the SQLServer:Locks - Number of Deadlocks/sec counter by using System Monitor. C. Trace the Lock:Deadlock event by using SQL Server Profiler. D. Trace the Lock:Deadlock Chain event by using SQL Server Profiler.

D

The sales department and the finance department are located in different buildings. The two departments are connected by a 2?megabit network. Both departments use SQL Server 2005. The sales department records all sales activities in a database named DB1. DB1 contains 380 tables and has a size of 10 GB. The finance department has read-only access to four tables in DB1 for reporting purposes. The finance department runs queries throughout the day and needs the data to be as up to date as possible. Users in the finance department tell you that report execution is slow. To optimize network capacity, your department decides to provide copies of these four tables to the finance department. You need to provide copies of these tables. You want to achieve this goal by using the copy method that uses the least amount of network bandwidth. What should you do? A. Mirror DB1 to the finance department. Configure mirroring for high-availability mode. B. Establish daily transaction log shipping of DB1 to the finance department. Use the STANDBY option to make the shipped database accessible to reporting. C. Establish daily snapshot replication to the finance department. Define a publication that contains the four tables as the only articles. D. Establish transactional replication to the finance department. Define a publication that contains the four tables as the only articles.

D

Which of the following is not recommended when performing a SQL Server upgrade? A. Create a series of checklists to ensure that all processes needed before, during, and after an upgrade have been completed. B. Reserve enough disk space for the extra copies of database files needed for the upgrade. C. Disable all startup stored procedures to prevent the stored procedures from firing multiple times during the upgrade. D. Configure system databases not to autogrow to prevent system databases from filling your disks during the upgrade process.

D

You are responsible for managing the database schema for an order entry application in a SQL Server 2005 database. After a design review, a member of the development staff asks you to add a new column named CommissionRate to the Product table. Because the actual commission rates for all products are not known at this time, each product has a default commission rate of 12 percent. The rate can be modified as necessary. You add the new column. You need to configure the table to assign the default value as efficiently as possible. What should you do? A. Create an INSERT trigger to assign the default value to each item in the table. B. Create a CHECK constraint to validate the data and to assign the default value to each item in the table. C. Create an UPDATE trigger to update the default value for each new item in the table. D. Create a DEFAULT constraint to assign the default value specifying the WITH VALUES argument.

D

You have a SQL Server 2005 computer named SQL1. Users report that the time required to run their queries on SQL1 is steadily increasing. You want to investigate this issue by using SQL Server Profiler to trace all user queries. You need to decide on which computer to run SQL Server Profiler, and where to store the trace. You must minimize the impact of this operation on SQL1 performance. What should you do? A. Run SQL Server Profiler from the console of SQL1. Store the trace in a table on SQL1. B. Run SQL Server Profiler over a Remote Desktop connection to SQL1. Store the trace in a file on a separate computer. C. Run SQL Server Profiler on a separate computer that is connected to SQL1. Store the trace in a table on SQL1. D. Run SQL Server Profiler on a separate computer that is connected to SQL1. Store the trace in a file on the computer that is running SQL Server Profiler.

D

You need to install a new production installation of SQL Server 2005. The server purchased for your installation has four CPUs. Which edition of SQL Server 2005 should you choose to make use of all the CPUs? A. SQL Server Express Edition B. SQL Server Workgroup Edition C. SQL Server Developer Edition D. SQL Server Standard Edition

D

You work in Dublin at the main office of Company.com. You are responsible for managing a SQL Server 2005 database. The sales department wants a report that compares customer activity in the previous quarter between the main office in Dublin and the branch office in Buenos Aires. They want the data sorted by surname and first name. You restore a recent backup of the Buenos Aires database onto your server. You write queries to build the report, ordering the data by the Surname and FirstName columns. You review the data and notice that the customer list from the Buenos Aires database is sorted differently. The sales department needs the revised data within 15 minutes for a presentation. You need to implement the fastest possible solution that ensures that the data from both databases is sorted identically. What should you do? A. Use the Copy Database Wizard to copy the data in the Buenos Aires database to a new database with the same collation as the Dublin database. B. Use the SQL Server Import and Export Wizard to copy the data from the Buenos Aires database into new tables, specifying the same collation as the Dublin database. C. Modify the format file to specify the same collation as the Dublin database. Import the table again. D. Modify the query on the Buenos Aires database to use the COLLATE setting in the ORDER BY clause. In the query, specify the same collation as the Dublin database.

D

You work with a SQL Server 2005 database that provides banking information for customers. You want customers to see banking reports that combine data that is retrieved from the database with real-time investment information that comes from a Web service provided by a third party. The investment information must be current when the reports are executed. You need to create the appropriate objects that support the reports. What should you do? A. Publish the data in the database as an XML Web service by using the FOR XML AUTO clause. B. Create a table to store the banking information for each customer. Create a trigger that fires when data is inserted into the table that joins with the data coming from the Web service. C. Create a Transact-SQL stored procedure that uses a temporary table to store the banking information for each customer. Update the table with the values from the Web service. D. Have a developer in Company.com create an assembly that calls the remote Web service. Create a CLR function by using the assembly. Call the CLR function and combine the results with banking information in the database.

D

You work with a database named DB1, which is located on a SQL Server 2005 computer. You discover that a table scan on Table1 in DB1 causes a slow query. Table1 is a very large table that is used frequently. You ascertain that the query contains the following statement. SELECT col1, col2 FROM table1 WHERE col3 = <value> You need to provide maximum query performance. Table1 must remain available to users. What should you do? A. Update all statistics on Table1 in DB1. B. Use the CREATE STATISTICS statement in DB1 to create missing statistics on col3 of Table1. C. Set the priority boost server option to 1. D. Execute the following statement.USE DB1GOCREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2) WITH (ONLINE = ON)GO E. Execute the following statement.USE DB1GOCREATE INDEX index1 ON table1(col3, col2, col1)

D

Your application must access data that is located on two SQL Server 2005 computers. One of these servers is named SQL1 and the other is SQL2. You have permissions to create a stored procedure on SQL1 to support your application. However, on SQL2 you only have permissions to select data. You write the stored procedure on SQL1. The stored procedure accesses SQL2 by using the OPENQUERY Transact-SQL statement. However, the query fails when executed. You need to troubleshoot the cause of the error. What should you do? A. Join the two servers by using the four-part syntax of server.database.schema.table. B. Reference SQL2 by using an alias. C. Add SQL2 as a remote server to SQL1. D. Add SQL2 as a linked server to SQL1.

D

Your department is responsible for creating a list of customer e-mail addresses from data in a SQL Server 2005 database. The list must include the date on which each customer was last contacted. The data in the result set must have column names. The list must be ordered by date of last contact, with the most recent contact first. The LastContact column is stored as a datetime. The date should be displayed as MM/DD/YYYY. A coworker develops the following query. SELECT email_address AS EmailAddress, CONVERT(nvarchar, lastcontact, 101) AS LastContact FROM Contact ORDER BY LastContact DESC You test this query and find that the data is displayed in the following order. EmailAddress LastContact [email protected] 01/24/2003 [email protected] 06/12/2005 [email protected] 07/13/2004 You need to modify the query so that the data is listed in the proper order. You want to achieve this goal without negatively affecting performance. What should you do? A. Change the ORDER BY clause as follows:ORDER BY CONVERT(nvarchar, lastcontact, 101) DESC B. Remove the column alias from the LastContact column. C. Change the ORDER BY clause as follows:ORDER BY CAST(lastcontact AS nvarchar(20)) DESC D. Change the alias on the LastContact column.

D