1、微软认证 70-228模拟试卷 5及答案与解析 1 You are the administrator of a SQL Server 2000 computer. Each evening after business hours, you perform database maintenance tasks. You create a Data Transformation Services package to populate the data warehouse. MAPI is not installed on the server. You want to schedule th
2、e DTS package to run each night. You want to attend to other duties while the DTS package is executing. You also want to be notified if the DTS package fails. What should you do? （ A） Select the DTS packages log package execution to SQL server check box. Create a SQL server event alert to notify you
3、 if the package fails. （ B） Select the DTS packages write completion status to event log check box. Create a SQL server event alert to notify you if the package fails. （ C） Configure the DTS package to include an on failure precedence constraint and a send mail task. Configure the send mail task to
4、notify you if the package fails. （ D） Configure the DTS package to include an on failure precedence constraint and a send mail task. Configure the execute process task to execute a not send command that will notify you if the package fails. 2 servers msdb database in an e-mail message to the adminis
5、trator 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 -Reattach the msdb database You test the job, and it fails on the first step. You need to ensure that the msdb database is
6、 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 tempo
7、rary 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 3 You are the database administrator for an accounting company. You are installing SQL Server 2000 on a ne
8、w Microsoft Windows 2000 Server computer. The computer will run two client/server database applications that your company has purchased. Each application uses a separate database. During the installation, you specify SQL_Latin1_General_CP1_CI_AI as the default collation for the SQL server computer.
9、After completing the installation, you discover that one of the new applications, named Financials, is sorting information incorrectly. You contact the application vendor and discover that the Financials application requires the SQL_Latin1_CP437_BIN collation. You need to configure the correct colla
10、tion for the applications without diminishing the performance of the SQL Server computer. What should you do? （ A） Rerun the SQL Server Setup and specify the SQL_Latin1_General_CP437_BIN collation. （ B） Run the rebuildm utility and specify the SQL_Latin1_General_CP437_BIN collation. （ C） Use the ALT
11、ER DATABASE statement to specify the SQL_Latin1_General_CP437_BIN collation for the Financials database. （ D） Use the ALTER TABLE statement to specify the SQL_Latin1_General_CP437_BIN collation for each table in the Financials database. 4 You are the database administrator of a SQL Server 2000 compu
12、ter. The server contains your companys Accounts database. Hundreds of users access the database each day. Because you have had power interruptions in the past, you want to perfect the physical integrity of the Accounts database. You do not want to slow down server operations. What should you do? （ A
13、） Enable the torn page detection database option for each database. （ B） Disable write caching on all disk controllers. （ C） Ensure that write caching disk controllers have battery backups. （ D） Create a database maintenance plan to check database integrity and make repairs each night. 5 You are the
14、 administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configured as shown in the exhibit. Users frequently need details about parts. Users search for data by using the following query.SELECT Parts.SKU, Locations.Description, Manufacturer.Name,
15、PartsLocations.Qty, PartsLocations.LastInventoriedFROM PartsINNER JOIN Manufacturer ON Parts.ManufacturerID= Manufacturer.ManufacturerIDINNER JOIN PartsLocation ON Parts.PartID = Parts.Location.PartsIDINNER JOIN Locations ON Parts.LocationID= Locations.LocationIDWHERE SKU?You need to accelerate quer
16、y response time. What should you do? （ A） Create a parameterised stored procedure to retrieve the data. （ B） Create a denormalized table that is maintained by triggers. （ C） Use ad hoc queries to retrieve the data. （ D） Create a nonclustered index on the primary key of each table. 6 You are the admi
17、nistrator of a SQL Server 2000 computer. The server contains a database that stores inventory data. Another database administrator has created a number of scheduled jobs to maintain the inventory database. Each weeknight the following jobs occur. - A BULK INSERT job imports data at 10:00 P.M. - Inde
18、xes are rebuilt at 10:15 P.M. - Data integrity checks are performed at 10:30 P.M. - A differential backup is performed at 10:45 P.M. - A DBCC SHRINKDATABASE job runs at 11:00 P.M. You notice that the final job often fails and returns the following error message: “Server Msg 3140, Level 16, State 3.
19、Could not adjust the space allocation for file inventory_data.“ You need to ensure that the final job runs without errors. What should you do? （ A） Create a new DBCC SHRINKFILE job to shrink the inventory_data file. Schedule the new job to run at the same time as the DBCC SHRINKDATABASE job. （ B） Mo
20、dify the DBCC SHRINKDATABASE job so that it uses DBCC SHRINKFILE statement to shrink each file individually. （ C） Increase the time between the data integrity checks and the differential backup. （ D） Increase the time between the differential backup and the DBCC SHRINKDATABASE job. 7 You are the adm
21、inistrator of a SQL Server 2000 computer in your companys personnel department. Employee data is stored in a SQL Server 2000 database. A portion of the database schema is shown in the exhibit. You want to create a text file that lists these data columns in the following format title, FirstName, Last
22、Name, WorkPhone, PositionName, DepartmentName.You want to create the text file as quickly as possible. You do not expect to re-create this file, and you want to avoid creating new database objects if possible.What should you do? （ A） Use the bcp utility to export data from each table to a separate t
23、ext file. Use format files to select the appropriate columns. Merge the data from each text file into a single text file. （ B） Create a view that joins data from all three tables include only the columns you want to appear in the text file. Use the bcp utility to export data from the view. （ C） Crea
24、te a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a local temporary table. Use the bcp utility to export data from the local temporary table to a text file. （ D） Create a SELECT query that joins the data from the appropr
25、iate columns in the three tables. Add an INTO clause to the query to create a global temporary table. Use the bcp utility to export data from the global temporary table to a text file. 微软认证 70-228模拟试卷 5答案与解析 1 【正确答案】 B 【试题解析】 Explanation: When jobs are completed (successfully or not), the subsystem
26、returns a result status (with optional messages) to SQL Server Agent. SQL Server Agent then records the completion status in both the operating systems application log and the job history table in SQL Server and optionally sends an e-mail report or an SQL Server event alert of the job status to the
27、designated operator. Incorrect Answers: A: We are interested to when the package has completed. The completion status, not the log package execution, should be used to trap the completion of a package. The execution status concerns Last run time, Last run status, Last run message, etc. C, D: The Mes
28、saging Application Programming Interface (MAPI) isnt installed. MAPI is needed for SQL Server to send e-mail. No e-mail can be send by SQL Server in this scenario. 2 【正确答案】 C 【试题解析】 Explanation: The current job fails since the detach msdb statement fails. It fails because detaching a database requir
29、es that no one is using the database. The msdb database is used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks. We want to send the msdb database. The msdb database could be detached if all processes that use it where terminated. Then we co
30、uld send the database file and reattach the msdb database. This would stop scheduled tasks from being run during this period. A better approach would to be to backup the database and send the temporary file instead. Note: A new SQL Server 2000 installation automatically includes six databases: maste
31、r, model, tempdb, pubs, Northwind, and msdb. The master, model, and tempdb databases cannot be detached. Incorrect answers: A: The problem that the SQL Service Agent is using the msdb database. This problem would still be there even user account running the SQL Server Agent service is changed. B: Th
32、e msdb database is used by the SQL Server Agent service and the database file would be locked. It cannot be sent directly in an e-mail message. D: The msdb database could be detached if all processes that use it where terminated. Then we could send the database file and reattach the msdb database. T
33、his would stop scheduled tasks from being run during this period. 3 【正确答案】 C 【试题解析】 Explanation: The collation of a database can be specified in the COLLATE clause of the ALTER DATABASE statement. This will change the default collation of the database that was specified when the database was created
34、, or to alter the collation for a database where the collation was not specified when the database was created and thus the default collation of the server was used. Incorrect Answers: A: The collation of a database can be altered using the COLLATE clause of the ALTER DATABASE statement. It is there
35、fore not necessary to reinstall the instance of SQL Server 2000 or to rerun the setup program. Furthermore, rerunning the SQL Server 2000 setup program would disrupt the performance of the SQL Server computer. B: The rebuildm utility alters the master database and can be used to change the collation
36、 settings for an instance of SQL Server 2000. However, it is only the Financial database that needs to be altered as it is financial application is the only application that uses the SQL_Latin1_General_CP437_BIN collation. D: The COLLATE clause can be specified at different levels. It can be specifi
37、ed for the database, a table, a column or an expression. In this scenario the collation can be altered at the database level as this will reduce administrative effort when new tables are added to the database. 4 【正确答案】 C 【试题解析】 Explanation: The scenario states that power interruptions have occurred
38、in the past. By buying a battery backup solution for the disk controllers the power interruption problem would be prevented. Incorrect Answers: A: Torn page detection is a reactive solution which would slow down performance since restores would have to be made. It would better to use a preventive so
39、lution. Note: Torn page detection: If a torn page is detected, the database must be restored from backup because it will be physically inconsistent. B: Caching is very import for performance. Disabling write-caching would be a bad idea. D: Repairing the database every night is not a good solution. D
40、atabase errors cannot be accepted. It is better to prevent the problem instead of trying to fix it after it has happened. 5 【正确答案】 D 【试题解析】 Explanation: A nonclustered index is similar to an index in a textbook. The data is stored in one place, the index in another, with pointers to the storage loca
41、tion of the data. The index contains entries describing the exact location in the table of the data values being searched for in the queries and should be used for columns that contain a large number of distinct values; queries that do not return large result sets; columns frequently involved in sea
42、rch conditions of a query that return exact matches; and decision-support-system applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns. Note: A bett
43、er solution, not listed here, would to create clustered indexes on the primary keys and nonclustered indexes on the foreign keys. Incorrect Answers: A: A parameterized query would not improve performance. Parameterized queries are SQL queries written for reusability. They contain parameter markers a
44、s placeholders for data that will change from execution to execution. In the Data Transformation Services (DTS) tasks that use parameterized queries. B: Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables. In this scenario four tables
45、 are being joined in the query. The normalization here is thus permissible. Note: Database normalization often improves query performance. When useful indexes are available, the SQL Server 2000 query optimizer can select rapid, efficient joins between tables. Normalization is advantageous as it aids
46、 faster sorting and index creation; enables a larger number of clustered indexes; allows for narrower and more compact indexes; reduces the number of indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements; and reduces null values and the opportunity for inconsiste
47、ncy, which increase database compactness. However, as normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance. C: An ad hoc query is a query that is composed for a particular task. T
48、he code in this scenario is an example of an ad hoc query. 6 【正确答案】 D 【试题解析】 Explanation: The DBCC SHRINKDATABSE cannot be executed until the previous job step, the differential backup, has been completed. We should increase the time between these two job steps, or even better configure the last job
49、 step to run only after the differential backup has been completed. Note: The DBCC SHRINKDATABASE statement shrinks data files on a per-file basis but shrinks log files as if all the log files existed in one contiguous log pool. The target size for the data and log files cannot be smaller than the minimum size of a file that was specified when the file was originally created, or the last explicit size set with a file size changing operation such as the ALTER DATABASE statement with the MODIFY FILE option or the DBCC SHRINKFILE statement. Incorrect Answers: A: The DBCC SHRINKDATA
