1、微软认证 70-228-试卷 3及答案解析(总分：14.00，做题时间：90 分钟)一、单选题(总题数：7，分数：14.00)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 sh
2、ould you do?（分数：2.00）A.Detach the database 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 f
3、rom the original server to the new server. 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.Sh
4、ut down the original server. Copy 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 cont
5、ains a database named Inventory. 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. （分数：2.00）A
6、.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 an InStock field as p
7、art 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 and PartsReceived tables.3.You are the administrator of a SQL s
8、erver 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 returns the following error message string or binary data would
9、 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 design exhibit.Table Schema （分数：2.00）A.Alter the data type o
10、f the description field to varchar(50).B.Alter the data type of the Special field to varchar(50).C.Alter 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 loca
11、tions table, create an INSTEAD OF trigger that truncates the Special field at 10 characters when 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. （分数：2.00）A.Compres
12、s 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 hard disk as part of a new filegroup.D.Map a network drive to the server. Create a new data file on the network dri
13、ve 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 SQL server 2000 computer. The server contains a database named MedicalRecords. Users access medical records by using the Pati
14、entID 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 options as shown in the exhibit. （分数：2.00）A.Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and sche
15、dule the job to run weekly.B.Select the Auto Update Statistics 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
16、2000 computers. You want to retrieve information 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 config
17、ured to use theSQL_Latin1_General_CP1_CI_AS 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?（分数：2.00）A.Use
18、 the rebuildm utility to rebuild the system database 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. Acc
19、ept the SQL_Latin1_General_CR437_BIN collation 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 S
20、QL Server 2000 on a new computer. 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
21、 is stored on a SQL Server 2000 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
22、SQL1?（分数：2.00）A.Use the ALTER DATABASE 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 c
23、heck box, and specify a collation name. Repeat this process once for each database on SQL2.微软认证 70-228-试卷 3答案解析(总分：14.00，做题时间：90 分钟)一、单选题(总题数：7，分数：14.00)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
24、 marketing database from one server and add it to the other as quickly as possible.What should you do?（分数：2.00）A.Detach the database 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
25、_attach_db stored procedure. B.Use the DTS export wizard to transfer all database objects from the original server to the new server. 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. Restor
26、e the backup in the new marketing database. Drop the database from the original server.D.Shut down the original server. Copy 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
27、 original server.解析：解析：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 server or disk the database must first be detached from the original server or disk; move
28、d 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 run UPDATE STATISTICS on all tables before detaching the database while the sp_attach_d
29、b 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 Server. It can also export data from SQL Server to an OLE DB data destination. It provides
30、 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 new location; the database must first be recreated in the destination as the backup hol
31、ds 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 a database from one server to another. Note: DISK INIT and DISK REINIT are features tha
32、t 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 DATABASE or ALTER DATABASE statement while DISK REINIT is used to restore device entries to ap
33、propriate 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 DATABASE and ALTER DATABASE create operating system files and databases in a single step.
34、 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.You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database has a Parts t
35、able 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. （分数：2.00）A.Add triggers to the PartsShipped and the PartsReceived tab
36、les 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 an InStock field as part of an aggregate query.D.Create stored procedures for m
37、odifying 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 and PartsReceived tables.解析：解析：Explanation: The Instock column of the Parts table must be updated when records are change
38、d 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 automatically when an UPDATE, INSERT, or DELETE statement is issued agains
39、t 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 constraints and defaults should be used instead whenever they provide a
40、ll 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 function cannot, by itself, be used to update the contents of one
41、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 calculations. However, a simple update of the InStock column, and not a ag
42、gregate 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 another table. Note: A view is a virtual table that allows dat
43、a 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 a table, join columns from multiple tables so that they appear
44、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 procedure is a group of Transact-SQL statements compiled into a single
45、 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 cursor that can be referenced outside the stored procedure. Store
46、d procedures assist in achieving a consistent implementation of logic across applications and can improve performance.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
47、report that when they insert new information in the Locations table, the upgrade application returns 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
48、shown in the trace exhibit. You examine the design of the locations table as shown in the table design exhibit.Table Schema （分数：2.00）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.Alter the data type of the CubicFeet field t
49、o 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 when the record is updated.解析：解析：Explanation: By examining the first exhibit we see that the Visual Basic application tries to update 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
