默认题库 - MCTS(70-431)

单选题

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 TRANSACTION END TRY BEGIN 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？（）

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.

查看答案

单选题

You work in Dublin at the main office of TestKing.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 Airesdatabase 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.

查看答案

单选题

TestKing.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 gent 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.

查看答案

单选题

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.

查看答案

多选题

公司A使用SQL SERVER 2005 数据库。数据库包含一个名为trg_InsertOrders的触发器，这个触发器在订单数据插入到订单表的时候触发。trg_InsertOrders触发器主要是负责在插入订单数据的时候检查相应的客户在客户表中存在。现在，你需要配置这个触发器，使他在导入数据的过程中不被触发。现要求你用最便捷的方法达到这个目标，你应该选择下面哪两个方法？（）

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&rsquo

查看答案

单选题

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

查看答案

单选题

公司A有一个由两个域DOM1和DOM2组成的网络。两个域之间没有信任关系。DOM1包含一个名为SQL1的服务器。DOM2包含一个名为SQL2的服务器。两个服务器都运行着SQL Server 2005。SQL1上的SQL Server实例使用服务帐户DOM1/SQLService1。SQL2上的SQL Server实例使用服务帐户DOM2/SQLService2。任何一个服务器上都不存在镜像端点。SQL1包含一个名为DB1的数据库。你想要使用高级别保护模式且在无见证的情况下将DB1镜像到SQL2。为此，你要创建数据库镜像端点并设置所有涉及到的Server实例的安全。你会怎样做？（）

A. 使用配置数据库镜像安全向导为DB1创建端点并设置安全性。建立端点使用SQL SERVER 验证。
B. 用Transact-SQL为每个Server实例配置数据库镜像端点及安全性。允许数据库镜像对出入连接使用加密证书。
C. 创建登录并将其添加到sysadmin组。设置每一个Server实例都使用SQL Server认证登录。然后使用数据库属性镜像页上配置安全性向导为DB1创建端点。
D. 连接到各个Server实例并执行以下语句。CREATE ENDPOINT mirroring STATE = STARTED AS TCP （ LISTENER_PORT=5022）FOR DATABASE_MIRRORING（AUTHENTICATION = WINDOWS NTLM， ROLE=PARTNER）；

查看答案

单选题

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

查看答案

单选题

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.
Create the view， specifying the WITH SCHEMABINDING option.
D. Create the view， specifying the WITH CHECK option.

查看答案

单选题

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.

查看答案