1、微软认证 70-228模拟试卷 7及答案与解析 1 You are the administrator of a SQL Server 2000 computer. You create several jobs on the server and schedule them to run during the evening. You configure an operator on the server and configure the jobs to send an e-mail to this operator if a job fails for any reason. While
2、 testing the jobs, you discover that e-mail messages are not being sent to the operator when a job fails. You use SQL Server Enterprise Manager to confirm that SQL Mail is started. You need to ensure that e-mail messages are sent to the operator whenever a job fails. What should you do? （ A） Configu
3、re SQLAgentMail to use a valid MAPI profile （ B） Configure SQL Mail to use a mailbox that delegates permissions to the operator （ C） Use the net send notification method to send the operator e-mail messages （ D） Create a job that executes the xp_startmail stored procedure Configure the job to run wh
4、enever SQL Server 2000 starts 2 You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to facilitate a large import of data, and rebuild them after the import is complete. Users report that response times from a stored procedure h
5、ave become slower. In the stored procedure, you examine the following code. CREATE PROCEDURE GetCustomer ( CustomerID Int ) AS SELECT FirstName, LastName, Address1, Address2, City, State, Zip, MAX(PurchaseDate) AS LastPurchase FROM Customers C JOIN Addresses A ON A.AddressID = C.AddressID JOIN Purch
6、ases P ON P.CustomerID = C.CustomerID WHERE C.CustomerID = CustomerID GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip You want to improve the performance of the procedure by using the least amount of administrative effort. What should you do? （ A） Recompile the stored procedure. （
7、 B） Update the database statistics. （ C） Check the consistency of the database and repair errors. （ D） Create a view and modify the stored procedure to use the view. 3 You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database is configured as s
8、hown in the exhibit. In the last six months, the database has grown by 4 GB. Users report that query response time has slowed. The options set up on the database are shown in the Database Options exhibit. You want to accelerate query response time. What should you do? （ A） Update the database statis
9、tics. （ B） Add indexes to the foreign key fields. （ C） Truncate the transaction log. （ D） Run the database maintenance plan wizard. （ E） Drop primary keys from all tables. 4 Exhibit: UPDATE EzonexamConsolidated SET District = Mexico WHERE RtvID = 45 You work as database administrator at E. You handl
10、e one SQL Server 2000 computer and one database. When users run the query in the exhibit above the receive the following error message: Server: Mrh3612, Level 16, Stats 1, Line 1 View or function EzonexamConsolidated is not updateable because it contains aggregates: How should you enable the query t
11、o run? （ A） Create a non-normalized EzonexamConsolidated table and populate the table with data from the base tables. （ B） The appropriate users should be granted UPDATE permissions on each base table. （ C） To enable updates on the composite tables create an INSTEAD OF trigger on the view. （ D） On e
12、ach base table add a cascading update trigger. 5 You are the administrator of a SQL server computer. The server is running SQL Server 6.5 and SQL Server 7.0. You install a named instance of SQL Server 2000, and then run the SQL server upgrade wizard. On the database selection screen, some of the SQL
13、 Server 6.5 databases are not listed. You cancel the SQL Server upgrade wizard. You need to ensure that the SQL Server 6.5 databases are listed in the wizard. What should you do? （ A） Uninstall SQL Server 7.0, and then rerun the SQL Server upgrade wizard. （ B） Run the Microsoft SQL Server-switch app
14、lication, and then rerun the SQL Server upgrade wizard. （ C） Create a data transformation services package that imports the databases from SQL Server 6.5 to SQL Server 2000, and then execute the package. （ D） Uninstall SQL Server 2000, and then reinstall SQL Server 2000 as the default instance. 6 Yo
15、u are the administrator of a SQL Server 2000 computer. The server contains a database that has the torn page detection database option enabled. Backups of the database are made daily. The server loses power for one minute. When power is restored, torn pages are detected. You notice in SQL Server Ent
16、erprise Manager that the database is marked suspect. You need to correct the problem. What should you do? （ A） Execute the DBCC CHECKDB statement, and then specify the PHYSICAL_ONLY option. （ B） Execute the DBCC CHECKDB statement, and then specify the REPAIR_REBUILD option. （ C） Execute the sp_reset
17、status stored procedure. （ D） Restore the suspect database from backups. 7 You are the administrator of three SQL Server 2000 computers at Five Lakes Publishing. One server, FLPSQL01, stores order data. You want to be able to use the other two servers, FLPSQL02 and FLPSQL03, to answer queries and ru
18、n report. The planned network configuration is shown in the exhibit.You want to use the database Maintenance Plan wizard to configure log shipping from FLPSQL01 to FLPSQL02 and FLPSQL03. You do not want users to add any new data on FLPSQL02 and FLPSQL03, but they must be able to run queries and repo
19、rts.Which two actions should you take? (Each correct answer presents part of the solution. Choose two) （ A） Set the database load state for each destination server to No recovery mode. （ B） Set the database load state for each destination server to standby mode. （ C） Enable the allow database to ass
20、ume primary role option for both destination servers. （ D） Enable the allow database to assume primary role option for one destination server and then disable this option for other destination server. （ E） Disable the allow database to assume primary role option for both destination servers. 8 You a
21、re the administrator of a SQL Server 2000 computer. The server is configured as shown in the Database Server Configuration exhibit. You need to create a new database named Inventory. Employees in your company will use the Inventory database to track inventory data. Users will require immediate respo
22、nses to queries that help them locate where parts are stored. The tables in the database will be configured as shown in the Database Schema exhibit. The database will consume 14 GB of disk space. You must configure the data files and transaction log to accelerate query response time.Which two course
23、s of action should you take? (Each correct answer represents part of the solution. Choose two.)A. On drive C, create a transaction log. On drive D, create a data file in the PRIMARY filegroup. On drive E, create a data file in the SECONDARY filegroup.B. On each drive, create a transaction log. On ea
24、ch drive, create a data file in the PRIMARY filegroup.C. On drive D, create a transaction log. On drive E, create a data file in the PRIMARY filegroup.D. On the PRIMARY filegroup, create all tables and all indexes.E. On the PRIMARY filegroup, create all tables. On the SECONDARY filegroup, create all
25、 indexes.F. On the PRIMARY filegroup, create the Parts table and its indexes. On the SECONDARY filegroup, create all other tables and their indexes. 微软认证 70-228模拟试卷 7答案与解析 1 【正确答案】 A 【试题解析】 Explanation: SQLAgentMail must be used when mail stored procedures are used to send notifications to operators
26、 by e-mail or pager. In order to use SQLAgentMail, a mail profile must be configured for SQL Server Agent to use to connect to the mail server and the mail server must be extended MAPI-compliant. Incorrect Answers: B: SQL Mail provides a way to receive e-mail messages generated by SQL Server 2000. M
27、essages can be triggered to provide information about the status of a job or a warning caused by an alert. SQL Mail can include a result set in a reply to e-mail messages that contain queries and allows SQL Server to send and receive e-mail by establishing a client connection with a mail server. How
28、ever, SQL Mail must have a mail profile created in the same user domain account or context that is used to start an instance of SQL Server 2000 as it looks for the defined mail profile in the domain account that triggered the e-mal message. C: Net send is used to sends messages to other users, compu
29、ters, or messaging names across a network. The Messenger service must be running to receive messages. It is not used for e-mail or paging purposes. D: The xp_startmail stored procedure is used to start a SQL Mail client session. If there is an existing mail session, xp_startmail does not start a new
30、 one. If mail is being used on the same computer on which SQL Server is also running, the mail client must be started either before xp_startmail is executed, or before SQL Server is started if SQL Mail is configured to automatically start when SQL Server starts. 2 【正确答案】 A 【试题解析】 Explanation: By rec
31、ompiling the stored procedure SQL Server will create a new execution plan for the statements in the procedure. SQL Server will detect that statistics are not up to date and it will automatically recreate the statistics during this process. The execution plan will be optimized for the current distrib
32、ution of data in the tables. Note: Stored procedures can be used to improve database query performance. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan that is cached on the server. When the database is changed by the add
33、ition of new indexes or when data in indexed columns are changed, the original query plans and stored procedures used to access the tables should be reoptimized by recompiling them. This optimization happens automatically the first time a stored procedure is run after SQL Server 2000 is restarted. I
34、t also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically occur until the next time the stored procedure is run after SQL Server is restarted. The stored procedure can be m
35、anually recompiled by using the sp_recompile system stored procedure. This forces the stored procedure to be recompiled the next time it is run. Incorrect Answers: B: SQL Server detects whether statistics are not up-to-date, and by default it automatically updates them during query optimization. By
36、recompiling the query the statistics will automatically be updated. Note: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Query optimization depends on the accuracy of the distributi
37、on steps. If there is significant change in the key values in the index, or if a large amount of data in an indexed column has been added, changed, or removed, or the table has been truncated and then repopulated, UPDATE STATISTICS should be rerun. C: Consistency of a database is maintained through
38、the use of constraints and triggers. These mechanisms are used to ensure that the database remains consistent by controlling the type of data that can be inserted into a column, through referential integrity checks, and through cascading referential constraints. D: A view is a virtual table that can
39、 be used to restrict users access to the base table, or to join various base tables so that they appear as a single table. However, creating a view that the stored procedure must access would require the creation of indexes on the view and will have to be updated when the base tables are updates. Th
40、is would require greater administrative effort. 3 【正确答案】 B 【试题解析】 Explanation: A FOREIGN KEY is a column or combination of columns used to establish and enforce a link between the data in two tables and should be indexed because changes to PRIMARY KEY constraints are checked with FOREIGN KEY constra
41、ints in related tables and FOREIGN KEY columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table. These indexes allow SQL Server 200
42、0 to find related data in the foreign key table quicker. Although creating this index is not a requirement, a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria. Incorrect Answers: A: SQL Server k
43、eeps statistics about the distribution of the key values in each index and uses these statistics to determine which index would be the best index to use for a particular query process. As the data in a column changes, index and column statistics can become out-of-date. This can hinder the query opti
44、mizers ability to make optimal decisions on how to process a query. As a result query performance will deteriorate. When statistics become out-of-date they can be updated automatically if the auto update statistics check box on the tables property dialog box is checked. The exhibit shows that this c
45、heck box is checked; therefore it is not out-of-date statistics that is hampering query performance. C: Old transaction log records that are no longer necessary for recovering or restoring a database should be deleted to make way for new log records. The process of deleting these old log records to
46、reduce the size of the logical log is called truncating the log. However, the active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete
47、transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. This truncation occurs automatically at the completion of any BACKUP LOG statement or every time a checkpoint is processed, provided the
48、database is using the Simple Recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. D: The Database Maintenance Plan Wizard can be used to set up the core maintenance tasks necessary to ensure that a database pe
49、rforms well, is regularly backed up, and is checked for inconsistencies. The Database Maintenance Plan Wizard creates a SQL Server 2000 job that performs these maintenance tasks automatically at scheduled intervals and can be used to reorganize the data on the data and index pages by rebuilding indexes with a new fill factor; compress data files by removing empty database pages; update index statistics to ensure the query optimizer has up-to-date information about the distribution of data values in the tables; perform internal consistency checks of the data and data pages wit
