1、微软认证 70-228模拟试卷 2及答案与解析 1 You are the administrator of two SQL Server 2000 computers. One of these servers contains a 4-GB database named marketing. You want to remove the marketing database from one server and add it to the other as quickly as possible. What should you do? （ A） Detach the database
2、from the original server by using the sp_detach_db stored procedure. Copy the database and the transaction log files to the new server, and attach them by using the sp_attach_db stored procedure. （ B） Use the DTS export wizard to transfer all database objects from the original server to the new serv
3、er. Drop the database from the original server. （ C） Run a full backup of the database on the original server. Create a new database named marketing on the new server. Restore the backup in the new marketing database. Drop the database from the original server. （ D） Shut down the original server. Co
4、py the database and the transaction log files to the new server. Use the DISK INIT and DISK REFIT statements to attach the data file to the new server. Drop the database from the original server. 2 You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventor
5、y. The database has a Parts table that has a field named InStock.When the parts have shipped, a table named PartsShipped is updated. When the parts are received, a table named PartsReceived is updated. The relationship of these tables is shown in the exhibit. You want the database to update the InSt
6、ock 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 t
7、ables. （ C） Use a view that creates an InStock field as part of an aggregate query. （ D） Create stored procedures for modifying the PartsShipped and PartsReceived tables that also modify the InStock field in the Parts table. Use these procedures exclusively when modifying data in the PartsShipped an
8、d PartsReceived tables. 3 You are the administrator of a SQL server 2000 computer. The server contains a database named Inventory.Developers at your company upgrade an inventory tracking application. Users report that when they insert new information in the Locations table, the upgrade application r
9、eturns the following error message string or binary data would be truncated. The statement has been terminated.When you use SQL profiler to trace the activity of the application, you receive the results as shown in the trace exhibit.You examine the design of the locations table as shown in the table
10、 design exhibit.Table SchemaYou need to configure the database to support both versions of the application without affecting server performance. What should you do? （ A） Alter the data type of the description field to varchar(50). （ B） Alter the data type of the Special field to varchar(50). （ C） Al
11、ter the data type of the CubicFeet field to float. （ D） In the locations table, create an INSTEAD OF trigger that truncates the description field at 10 characters when the record is updated. （ E） In the locations table, create an INSTEAD OF trigger that truncates the Special field at 10 characters w
12、hen the record is updated. 4 You are the administrator of a SQL Server 2000 computer. The server contains a database named Accounting. The data files are configured as shown in the exhibit. The database has grown to 7 GB. The server has no more available disk space. You need to make more disk space
13、available in the accounting database without affecting system performance.What should you do? （ A） Compress drive E. （ B） Add a new hard disk to the server. Create a new file on the new hard disk as part of the PRIMARY filegroup. （ C） Add a new hard disk to the server. Create a new file on the new h
14、ard disk as part of a new filegroup. （ D） Map a network drive to the server. Create a new data file on the network drive as part of the PRIMARY filegroup. （ E） Map a network drive on the server. Create a new data file on the network drive as part of a new filegroup. 5 You are the administrator of SQ
15、L server 2000 computer. The server contains a database named MedicalRecords. Users access medical records by using the PatientID field. This field is the clustered primary key for the Patients table. When users try to access medical records, the database responds slowly. You examine the database opt
16、ions as shown in the exhibit. You want to accelerate query response time and minimize administrative overhead. How should you reconfigure the database? （ A） Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and schedule the job to run weekly. （ B） Select the Auto Update Stati
17、stics check box. （ C） Run the database maintenance plan wizard, and accept the default settings. （ D） Rebuild the primary key as a nonclustered primary key. （ E） Clear the Auto Create Statistics check box. 6 You are the administrator of several SQL Server 2000 computers. You want to retrieve informa
18、tion from an archived inventory database. You have a full tape backup of the database. The backups header information shows that the backup uses the SQL_Latin1_General_CR437_BIN collation. However, the existing SQL server computers in your office are configured to use the SQL_Latin1_General_CP1_CI_A
19、S collation. You do not want to join tables in the inventory database with tables in other databases. You need to restore the inventory database to a SQL Server 2000 computer by using the least amount of administrative effort. What should you do? （ A） Use the rebuildm utility to rebuild the system d
20、atabase on an existing SQL server computer. Configure all the databases on that server to use the SQL_Latin1_General_CR437_BIN collation. Restore the inventory database to the server. （ B） Restore the inventory database to an existing SQL server computer. Accept the SQL_Latin1_General_CR437_BIN coll
21、ation for that database. （ C） Install a new named instance of SQL Server 2000 on an existing SQL Server 2000 computer. Configure the named instance to use the SQL_Latin1_General_CR437_BIN collation. Restore the inventory database to the named instance. （ D） Install SQL Server 2000 on a new computer.
22、 Configure the new server to use the SQL_Latin1_General_CR437_BIN collation. Restore the inventory database to the new server. 7 You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad hoc distribution queries against a database that is stored on a SQL Server 2000
23、computer named SQL2. SQL2 contains several databases, and each of these databases uses a different collation. You want to ensure that comparisons in distributed queries are evaluated correctly. You also want to minimize administrative overhead. How should you configure SQL1? （ A） Use the ALTER DATAB
24、ASE statement to change the collation of the databases on SQL1. （ B） Add SQL2 as remote server. （ C） Add SQL2 as a linked server. Select the Use Remote Collation check box, and do not specify a collation name. （ D） Add SQL2 as a linked server. Select the Use Remote Collation check box, and specify a
25、 collation name. Repeat this process once for each database on SQL2. 微软认证 70-228模拟试卷 2答案与解析 1 【正确答案】 A 【试题解析】 Explanation: Detaching and attaching databases is sometimes a useful technique for quickly moving a database from one computer to another. To move a database, or database file, to another se
26、rver or disk the database must first be detached from the original server or disk; moved to the other server or disk and then attached on the server or disk. In addition the new location of the moved file(s) must be specified. The sp_detach_db stored procedure is used to detach the database and can
27、run UPDATE STATISTICS on all tables before detaching the database while the sp_attach_db stored procedure is used to attach the database in its new location. Incorrect Answers: B: DTS can import data from a text file or from an OLE DB data source such as a Microsoft Access 2000 database into SQL Ser
28、ver. It can also export data from SQL Server to an OLE DB data destination. It provides for high-speed data loading from text files into SQL Server tables. However, it is not the fastest way to move a whole database from one server to another. C: Before a database can be restored from a backup to a
29、new location; the database must first be recreated in the destination as the backup holds only the data files that belong in the various tables. It does not hold the table definitions. This is not the fastest method to move a database. D: The DISK INIT and DISK REFIT commands cannot be used to move
30、a database from one server to another. Note: DISK INIT and DISK REINIT are features that are used in a SQL Server 6.x. in SQL Server 6.x, DISK INIT is used to create database or transaction log devices to store the specified database or transaction log when DISK INIT followed either a CREATE DATABAS
31、E or ALTER DATABASE statement while DISK REINIT is used to restore device entries to appropriate system tables when the device entry was missing from sysdevices. In SQL Server 2000 the CREATE DATABASE and ALTER DATABASE statements allow for the creation of separate data and log files. Both CREATE DA
32、TABASE and ALTER DATABASE create operating system files and databases in a single step. It is thus recommended that all references to DISK INIT should be removed in SQL Server 2000 and replaced with references to either CREATE DATABASE or ALTER DATABASE. 2 【正确答案】 A 【试题解析】 Explanation: The Instock co
33、lumn of the Parts table must be updated when records are changed in the other two tables PartsReceived and PartsShipped. The only way to accomplish this is to use triggers on the PartsReceived and PartsShipped tables. Note: Triggers are a special class of stored procedure defined to fire automatical
34、ly when an UPDATE, INSERT, or DELETE statement is issued against a table or view. They are powerful tools that can be used to enforce business rules automatically when data is modified. Triggers can extend the integrity checking logic of SQL Server constraints, defaults, and rules, although constrai
35、nts and defaults should be used instead whenever they provide all the needed functionality. In this scenario an AFTER UPDATE trigger can be used to update the tables to update the PartsShipped and the PartsReceived tables that update the InStock column in the parts table. Incorrect Answers: B: A fun
36、ction cannot, by itself, be used to update the contents of one table when changes are made to another table. Note: A user-defined function is a subroutine that is made up of one or more Transact-SQL statements and can be used to encapsulate code for reuse. It can be used to run aggregate calculation
37、s. However, a simple update of the InStock column, and not a aggregate function, is required when changes are made to the PartsShipped and PartsReceived tables. This can be accomplished through the use of triggers. C: A view cannot be used to update the contents of one table when changes are made to
38、 another table. Note: A view is a virtual table that allows data to be accessible through it rather than through the base table. A view can be referenced through Transact-SQL statements in the same way a table is. In addition a table can be used to restrict a user to specific rows and/or columns in
39、a table, join columns from multiple tables so that they appear as a single virtual table and can aggregate information instead of supplying details to the user. D: A function cannot, by itself, be used to update the contents of one table when changes are made to another table. Note: A stored procedu
40、re is a group of Transact-SQL statements compiled into a single execution plan. Stored procedures can return data as output parameters; return codes; a result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure; or a global cu
41、rsor that can be referenced outside the stored procedure. Stored procedures assist in achieving a consistent implementation of logic across applications and can improve performance. 3 【正确答案】 A 【试题解析】 Explanation: By examining the first exhibit we see that the Visual Basic application tries to update
42、 the Description field. Apparently there is a problem with this field. The description field must be adapted. We increase the field to accommodate for both versions of the application. Incorrect Answers: B: The description field, not the special field, is the problem. C: The error message doesnt ind
43、icate a problem in float column, more likely a character column. D: This would lose data. E: The description field, not the special field, is the problem. 4 【正确答案】 B 【试题解析】 Explanation: When a SQL Server 2000 server runs out of disk space, additional hard drives must be added to the server. A file c
44、an then be created on the new disk as part of an existing filegroup or as part of a new filegroup. In this scenario a new file should be placed on the new hard disk for best performance. This file can either be placed in the PRIMARY filegroup or a new filegroup could be created. The performance gain
45、 would be equal in both cases, but if we use a new filegroup we would have to configure it so that it would be used. Incorrect Answers: A: SQL Server data files cannot be installed on a system that uses compression. C: When a SQL Server 2000 server runs out of disk space, additional hard drives must
46、 be added to the server. A file can then be created on the new disk as part of an existing filegroup or as part of a new filegroup. To use the new filegroup we would have to configure it (add data to it or make it the default filegroup). Without configuration the new filegroup will not be used. All
47、data would still be added to the default PRIMARY filegroup. D: Mapping a network drive to the server will increase the disk space available to the database but query time will be limited to the network environment and network transfer speeds. This will hamper query response time. E: Mapping a networ
48、k drive to the server will increase the disk space available to the database but query time will be limited to the network environment and network transfer speeds. This will hamper query response time. 5 【正确答案】 B 【试题解析】 Explanation: The Auto Update Statistics option is selected by default but in the
49、 Exhibit it has been disabled. Apparently the statistics are out-of-date and the queries are running slowly. The Auto Update Statistics option should be enabled to increase performance and minimize administrative overhead. Note: SQL Server keeps 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 optimizers ability to make optimal decisions on how to pro
