|
发信人: reynolds(雷龙.DBA中) 整理人: reynolds(2002-05-30 13:24:04), 站内信件 |
| 11. You are the administrator of an SQL Server 2000 computer. Each night, you need to send a copy of the server’s MSDB database as an e-mail message to the administrator of another SQL server computer.
You create a job that contains the following steps: > Detach the MSDB database. > Send the database to the administrator in an e-mail message. > Re-attach the MSDB database. You test the job, and it fails on the first step. You need to ensure that the MSDB database is mailed to the administrator every night. What should you do? A. Ensure that the SQLServerAgent service is running under a user account that has database owner access to the MSDB database. B. Delete the first and last steps in the job. C. Configure the job to back up the MSDB database to a temporary file. Send the file to the administrator in an e-mail message. D. Insert a new job step before the first step. Configure the new step to terminate all processes that use the MSDB database. Answer: C Reason: MSDB作为SQL SERVER的系统数据库之一，是没有道理随意分离的，你可以到企业管理器中去看，你会发现分离数据库是灰色的，因此分离-备份-附加的工作原理是错误的、无法实现的，即使进行了D操作也是如此。 12. You are the administrator of a SQL Server 2000 computer. The server contains two databases. A client/server application accesses one of the databases. A different client/server application accesses the other database. Each application uses several stored procedures to query and update its database. Users report that both applications respond slowly. You use SQL Profiler to monitor activity on the server. You receive results of the trace as shown below: Event Class Test Date SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN SQL:BatchCompleted Update Customer Set CustomerZip = …… SQL:BatchCompleted Update Products Set SKU = ‘23232323….. SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN SQL:BatchCompleted Update Customers Set CustomerZip= You need to improve the performance of the applications. What should you do? A. Modify the applications so that they use views to query data. B. Modify the stored procedures so that they select data into temporary tables. C. Re-create the stored procedures so that they do not contain the WITH RECOMPILE option. D. Disable the ‘Autoupdate statistics’ option in both databases. Answer: C Reason: ====================================================================== 重新编译存储过程 在用诸如添加索引或更改索引列中的数据等操作更改数据库时，应通过重新编译访问数据库表的原始查询计划使其得到重新优化。在重新启动 Microsoft® SQL Server™ 2000 后第一次运行存储过程时自动发生该优化。当存储过程使用的基础表更改时也会发生优化。但是如果添加了存储过程可能从中受益的新索引，将不发生自动优化，直到下一次 SQL Server 重新启动后再运行该存储过程时为止。 SQL Server 提供三种重新编译存储过程的方法： >sp_recompile 系统存储过程强制在下次运行存储过程时进行重新编译。 >创建存储过程时在其定义中指定 WITH RECOMPILE 选项，表明 SQL Server 将不对该存储过程计划进行高速缓存；该存储过程将在每次执行时都重新编译。当存储过程的参数值在各次执行间都有较大差异，导致每次均需创建不同的执行计划时，可使用 WITH RECOMPILE 选项。此选项并不常用，因为每次执行存储过程时都必须对其进行重新编译，这样会使存储过程的执行变慢。 >在执行存储过程时指定 WITH RECOMPILE 选项，可强制对存储过程进行重新编译。仅当所提供的参数不典型，或者自创建该存储过程后数据发生显著更改时才应使用此选项。 此题目属于第三种！ ====================================================================== 13. You are the administrator of a database that contains 64 lookup tables. These tables store static data that should not change. However, users report that some of this data is being changed. You need to prevent users from modifying the data. You want to minimize changes to your security model and to your database applications. How should you modify the database? A. Create a filegroup named LOOKUP. Move the lookup tables to this filegroup. Select the read-only check box for the filegroup. B. Create a database role named datamodifier. Grant SELECT permissions to the datamodifier role. Add all users to the role. C. Deny INSERT, UPDATE, and DELETE permissions for all users. Create stored procedures that modify data in all tables except lookup tables. Require users to modify data through these stored procedures. D. Create a view of the lookup tables. Use the view to allow users access to the lookup tables. Answer: A Reason: A是最简单的方法，操作如下：选择该数据库-属性-文件组。使用权限配置方式也可以，不过操作均不可能在3步以内完成，所以和题目所要求的最小管理量不符合，而且以上其他选项单独操作均不可能实现题目的要求。 14. You are the administrator of an SQL Server 2000 computer. The server contains a database named Inventory. The database has a parts table that has a field named InStock. When parts are shipped, a table named PartsShipped is updated. When parts are received, a table names PartsReceived is updated. The relationship of these tables is shown below: See http://www.cheet-sheets.com/228/image2.jpg 
You want the database to update the Instock field automatically. What should you do? A. Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the parts table. B. Create a user-defined function that calculates current inventory by running aggregate queries on the PartsShipped and PartsReceived tables. C. Use a view that creates InStock as a part of an aggregate query. D. Create stored procedures for modifying the PartsReceived and the PartsShipped tables that also modify the InStock field in the Parts table. Use these procedures exclusively when modifying data in the PartsReceived and the PartsShipped tables. Answer: A Reason: 做这样的连带工作Trigger是非常合适的。计算累加做什么？干掉B和C。排他运作存储过程是一种不科学的做法，远没有Trigger对系统的负面影响来的小，性能好。 15. You are the administrator of a SQL Server 2000 computer. You are creating a database named RetailImport to use as an intermediate data store for a data warehouse. Each night you must import daily sales data into the database from SQL Server 2000 computers in 120 locations. After the data is moved into the data warehouse, the tables are truncated. The database schema is shown below: See http://www.cheet-sheets.com/228/image3.jpg 
You want to configure the data import processor so that you minimize the time needed to import the sales data and administer the database. What should you do? A. Use the simple recovery model and the FOR LOAD option to create the database. Create a data transformation services package that uses the BULK INSERT statement to copy the sales data. B. Index the foreign key fields in the child tables. C. Create a data transformation services (DTS) package that uses a Data Driven Query task to copy the sales data. D. Collect the sales data by using a distributed transaction that inserts the data from all 120 retail locations into the RetailImport database in a single transaction. E. On the servers in the retail locations, create stored procedures that submit the updates to the RetailImport database each night as SQLXML updategrams. Answer: A Reason: 多点数据汇总，除了选择DTS还能选择谁？（想起了动力火车的“除了爱你还能爱谁”...）。将数据库设定为简单恢复模式可以减少日志量，从而加快速度，减少时间。 ---- / <>< o /| / <>< (o / |/| ) ) <@ < ( O )( \ |\| <>< ) <>< () \| \ \ 每天在网易游水的鱼 |
|
[关闭][返回] |