1、微软认证 70-228模拟试卷 8及答案与解析 1 You are the administrator of a SQL Server 2000 computer. The server contains a database named sales. Your company uses the database to store the sales departments responses to requests for price quotations. The database is configured as shown in the exhibit. Developers in y
2、our company create an application used for saving quotations. The application executes the following transact-SQL statement: UPDATE QuotationSummary SET CustomerName = Jean-Paul Deloria WHERE QuotationID = 12 When the application executes the statement, the developers receive the following error: Se
3、rver: Mrh4403, Level 16, Stats 1, Line 1 View or function QuotationSummary is not updateable because it contains aggregates: You want developers to be able to use this UPDATE statement without generating errors. What should you do? （ A） Create an INSTEAD OF trigger on the view to update the composit
4、e tables. （ B） Grant the developers UPDATE permissions on each base table. （ C） Add a cascading update trigger on each base table. （ D） Create a QuotationSummary table and populate the table with data from the base tables. 2 You are the administrator of SQL Server 2000 computer. The server contains
5、a database named Inventory. The database has a set of tables that track the location of parts. The tables are configured as shown in the Database Schema exhibit. You examine the indexes in the PartsLocations table. The indexes are configured as shown in the Indexes exhibit.You also examine the conti
6、guity of the PK_partsLocations index and receive the following results:DBCC SHOWCONTIG scanning PartsLocations table.Table: PartsLocations (1); index ID: 1, database ID: 6TABLE level scan performed.- Pages Scanned: 3000- Extents Scanned: 750- Extent Switches: 502- Avg. Pages per Extent: 4.0- Scan De
7、nsity Best Count:Actual Count.: 33.33% 1:3- Logical Scan Fragmentation.: 33.33%- Extent Scan Fragmentation: 33.33%- Avg. Bytes Free per Page.: 1166.0- Avg. Page Density (full).: 85.77%You need to improve the performance of the queries that join this table to the Parts table and the Locations table.
8、What should you do? （ A） Re-create the PK_PartsLocations index as a clustered index. （ B） Re-create the IX_PartsLocations_PartsID as a clustered index. （ C） Re-create the IX_PartsLocations_LocationId as a clustered index. （ D） Re-create the PK_PartsLocations index as a nonclustered index. 3 You are
9、the administrator of a SQL Server 2000 computer. The server contains a database that is heavily indexed and that company users query extensively. The database has grown and query response time has slowed. The database is stored in a single data file. You want to accelerate query response time. What
10、should you do? （ A） On a new hard disk, create a new filegroup. Drop the existing nonclustered indexes, and then re-create them on the new filegroup. （ B） On a new hard disk, add a new file to the PRIMARY filegroup. Drop the existing nonclustered indexes, and then re-create them on the PRIMARY fileg
11、roup. （ C） On the existing hard disk, create a new filegroup. Drop the existing nonclustered indexes, and then re-create them on the new filegroup. （ D） On the existing hard disk, add a new file to the primary filegroup. Drop the existing nonclustered indexes, and then re-create them on the PRIMARY
12、filegroup. 4 You are the administrator of SQL Server 2000 computer named FABSQL2K01. You create a Data Transformation Services package that contains definition for two transform data tasks.The tasks gets data from two text files named Customers and Sales. The DTS package is configured as shown in th
13、e exhibit.You do not want the DTS package to add any data to the server unless both transform data tasks complete successfully.You select the use transactions check box of DTS package and the Join transaction of present check box of both transform data tasks. You then select the fail package on step
14、 failure check box of both transform data tasks.You want to use the DTS package to import data from the text files to the server. You want to ensure that changes are committed if all imported data is successfully transformed. What should you do? （ A） Select the commit on successful completion check
15、box of the DTS package. （ B） Select the commit transaction on successful completion of this step check box of the customers transform data task. （ C） Select the commit transaction on successful completion of this step check box of the sales transform data task. （ D） Select the commit transaction on
16、successful completion of this step check box of both transform data tasks. 5 You are the administrator of a 12 SQL Server 2000 computers. These servers are located in company offices throughout the world. You configure a multiserver job on the server in your companys New York City office. You config
17、ure this server as a master server, and designate the remaining 11 servers as target servers. After the job completes successfully, you decide to set up a new SQL Server 2000 computer as the master server. The new server will be named JobMaster and the other 12 servers will be target servers. You ne
18、ed to make the New York City server a target server instead of a master server. What should you do? （ A） On the New York City server, defect all 11 target servers. On the JobMaster server, enlist the 12 servers as target servers. （ B） On the JobMaster server, enlist all other servers as target serve
19、rs. （ C） On the New York City server enlist the JobMaster server as a target server. Transfer the multiserver jobs from the New York City server to the JobMaster server. （ D） On the JobMaster server, enlist the New York City server as a target server. On the JobMaster server, enlist the other 11 ser
20、vers as target servers. On the JobMaster server, defect the New York City server. 6 You are the administrator of a new Microsoft Windows 2000 Server computer named CPSRV1.CPSRV1 is a member server in your companys Windows NT 4.0 domain, which is named CPDOMAIN. A separate Windows NT domain named CPU
21、SERS contains all of your companys user accounts. A trust relationship exists between the CPDOMAIN domain and the CPUSERS domain.You are installing SQL Server 2000 on CPSRV1.You specify a service account for each SQL Server service as shown in the following table: After the installation is complete,
22、 the MSSQLServer service and the SQLServerAgent service do not start. You need to configure the services to start without changing any security settings in either domain.What should you do? （ A） Configure the services to run under the local system account （ B） Add CPSRV1 to the CPUSERS domain （ C） A
23、dd CPUSERSsqlsvr and CPUSERSsqlagent to the local administrators group on CPSRV1 （ D） Add CPUSERSsqlsvr and CPUSERSsqlagent to the CPUSERSDomain Admins groups. （ E） Add CPUSERSsqlsvr and CPUSERSsqlagent to the CPUSERSDomain users groups. 7 Exhibit: BULK INSERT Ezonexam.dbo.employees FROM tkafricefub
24、ar512.txt WITH DATAFILETYPE = char, FIELD/TERMINATOR = t, ROWTERMINATOR = n, TABLOCK You work as database administrator at E. You handle one SQL Server 2000 computer. How should you improve the performance of the query in the exhibit above? A. Extend the BULK INSERT statement with the BATCHSIZE opti
25、on set it to equal to 5 percent of the number of rows to be loaded. B. Extend the BULK INSERT statement with the BATCHSIZE option set it to equal to 10 percent of the number of rows to be loaded. C. Extend the BULK INSERT statement with the ROWS_PER_BATCH option set it to equal to 5 percent of the n
26、umber of rows to be loaded. D. Extend the BULK INSERT statement with the ROWS_PER_BATCH option set it to equal to 10 percent of the number of rows to be loaded. E. Insert a line before the BULK INSERT statement that drops the indexes for the customers table. Insert a line after the BULK INSERT state
27、ment that recreates the indexes for the customers table. F. The TABLOCK option should be removed. G. The ROWTERMINATOR line should be removed. 8 You are the administrator of Microsoft Windows 2000 computer. You are preparing to install SQL Server 2000 on the computer.Your company contains a variety
28、of client computers that will connect to the SQL server 2000 computer by using a specific Net-Library, as shown in the following table. You need to allow the client computers to connect to the SQL Server 2000 computer. You also want to minimize the number of configuration changes required on the cli
29、ent computers.Which three actions should you take? (Each correct answer presents part of the solution. Choose three)A. Install SQL Server 2000 as a named instance.B. Install SQL Server 2000 as the default instance.C. Configure the new instance for Mixed Mode Authentication.D. Configure the new insta
30、nce for Windows Authentication.E. Configure the server to use the Named Pipes, IPX/SPX, and TCP/IP Net-Libraries.F. Configure the server to use the Multiprotocol Net-Library. 微软认证 70-228模拟试卷 8答案与解析 1 【正确答案】 A 【试题解析】 Explanation: SQL Server 2000 uses INSTEAD OF triggers and partitioned views to enhan
31、ce the class of updateable views. INSTEAD OF triggers can be created on a view in order to make a view updateable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger allows the user to specify the set of actions that need to ta
32、ke place in order to process the data modification statement. Thus, if an INSTEAD OF trigger exists for a view on a given data modification statement, the corresponding view is updateable through that statement. If the view is a partitioned view, the view is updateable, subject to certain restrictio
33、ns. Incorrect Answers: B: Before a user can use a database; they must be given certain permissions that are determined by the type of activity they will be permitted on the database. To run UPDATE statements against a database the user must be granted UPDATE permissions. This can be specified at the
34、 table or database level. In this scenario, granting the permission at the database level would require less administrative effort. However, the error message is not related to permissions but to the updateability of the table. C: Cascading referential integrity constraints makes it possible to defi
35、ne the actions SQL Server 2000 takes when a user attempts to delete or update a key to which existing foreign keys point. Cascading referential integrity constraints fire AFTER triggers after all of the cascading referential actions directly caused by the original DELETE or UPDATE are performed. D:
36、Creating a table that combines the composite tables that the view is based on would deny us the ability to use the view to return aggregated column values. 2 【 正确答案】 A 【试题解析】 Explanation:The output of the DBCC SHOWCONTIG shows that the clustered index of the table is fragmented. Both Logical Scan Fr
37、agmentation and Extent Scan Fragmentation are above 10%. The clustered index PK_PartsLocations should be rebuilt. Note: In general, you need to do very little in terms of index maintenance. However, indexes can become fragmented. You can use the DBCC SHOWCONTIG command to report on the fragmentation
38、 of an index. You can use either the Extent Scan Fragmentation value or the Logical Scan Fragmentation value to determine the general level of fragmentation in a table. Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a tables fragmenta
39、tion level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). Incorrect Answers: B, C: There already exists a clustered index, the PK_PartsLocations index. A table can only contain one clustered index. D: A clustered index is faster th
40、at a non-clustered index. We should rebuild the clustered index, not create a nonclustered index instead. 3 【正确答案】 A 【试题解析】 Explanation: With SQL Server 2000 it is possible to create tables or indexes on specific filegroups. This allows us to control where the databases tables and indexes are physic
41、ally located as filegroups can be placed on different hard drives. Placing heavily accessed tables in one filegroup and the tables nonclustered indexes in another filegroup on different physical disk can improve database performance because it will allow separate threads to access the tables and ind
42、exes. However, a table and its clustered index cannot be separated into different filegroups as the clustered index determines the physical order of the data in the table. Incorrect Answers: B: SQL Server 2000 can operate effectively without filegroups. In this case, all files are included in the pr
43、imary filegroup and SQL Server 2000 can allocate data anywhere in the database. Furthermore, filegroups are not the only method that can be used to distribute I/O across multiple drives. However, the nonclustered index is added to the filegroup rather than the new file in this solution. This is thus
44、 not the best solution. C: This solution does not take advantage of the possible performance gains that can be realized by placing data tables and indexes on separate physical disks. D: This solution does not take advantage of the possible performance gains that can be realized by placing data table
45、s and indexes on separate physical disks. This would require that a new file group be created. 4 【正确答案】 A 【试题解析】 Explanation: It is possible to set the Commit on successful package completion check box on the Advanced Tab of the DTS Properties dialog Box. This check box specifies that if each indivi
46、dual SQL statement is a transaction completes successfully, the transaction will automatically be committed; and if the statement has an error, the statement is rolled back. When this check box is cleared, the DTS connections operate in implicit transaction mode and the first SQL statement begins a
47、transaction that remains in effect until DTS commits it or rolls it back. The next SQL statement executed after any commit or rollback starts a new transaction. Incorrect Answers: B: The Commit transaction on successful completion of this step check box treats each step in a DTS package as separate.
48、 Thus, selecting the Commit transaction on successful completion of this step check box of the customers transform data task would result in this task being committed regardless of the progress of the sales transformation data task. C: The Commit transaction on successful completion of this step che
49、ck box treats each step in a DTS package as separate. Thus, selecting Commit transaction on successful completion of this step check box of the sales transform data task would result in this task being committed regardless of the progress of the customers transformation data task. D: The Commit transaction on successful completion of this step check box treats each step in a DTS package as separate. Thus, select the commit transaction on successful completion of this step check box of both transform data tasks would result in the two parts bei
