1、微软认证 70-228-试卷 1及答案解析(总分：8.00，做题时间：90 分钟)一、单选题(总题数：4，分数：8.00)1.You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. Users report that several storage locations in the UnitsStored field contain negative numbers. You examine the databases table struc
2、ture. The table properties are configured as shown in the exhibit. （分数：2.00）A.ALTER TABLE dbo.storagelocations ADD CONSTRAINTS CK_storagelocations_UnitsStored CHECK (UnitsStored= 0)B.CREATE TRIGGER CK_UnitsStored On StorageLocations FOR INSERT, UPDATE AS IF INSERTED, UnitsStored = 0 GO Sp_bindrule O
3、K_UnitsStored, StorageLocations, UnitsStored GOD.CREATE PROC UpdateUnitsStored (StorageLocationID int, UnitsStored bigint)AS IF UnitsStored = 0) B.CREATE TRIGGER CK_UnitsStored On StorageLocations FOR INSERT, UPDATE AS IF INSERTED, UnitsStored = 0 GO Sp_bindrule OK_UnitsStored, StorageLocations, Uni
4、tsStored GOD.CREATE PROC UpdateUnitsStored (StorageLocationID int, UnitsStored bigint)AS IF UnitsStored 0 DATSERROR (50099, 17) ELSE UPDATE storaheLocations SET UnitsStored = UnitsStored WHERE StorageLocationID = storageLocationID解析：解析：Explanation: CHECK constraints are used to enforce domain integr
5、ity by restricting the values that are accepted by a column. They determine which values are valid by testing a logical expression that is not based on data in another column. The problem posed by this scenario can be solved through the implementation of such a constraint. In this solution the CHECK
6、 constraint is added in line 1 and the CHECK expression is specified in line 3. The expression specifies that the values contained in the UnitsStored column must be more than or equal to 0. Incorrect Answers: B: This solution uses a trigger to perform the CHECK constraint, which a trigger can do. Ho
7、wever, it is recommended that triggers only be used when the CHECK constraint cannot meet the functionality required by the application. This is thus not the best answer. C: In this solution creates a new table is created with the CREATE TABLE statement. This is inappropriate to the problem, as it d
8、oes not address the insertion of negative values in the existing table. D: This solution creates a stored procedure to perform the CHECK constraint.2.You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. You perform full database backups every two days.
9、 You also run regular database consistency checks on the server. The most recent check of the Sales database returns the following message.“CHECKDB found 0 allocation errors and 9 consistency errors in the table Orders (object ID 214575782).”You want to correct the data integrity errors while minimi
10、zing the amount of data lost. What should you do?（分数：2.00）A.Disconnect users from the Sales database. Enable the single user database option. Execute the DBCC CHECKTABLE statement for the Orders table, and specify the REPAIR_REBUILD option. B.Disconnect users from the Sales database. Enable the DBO
11、use only database option. Execute the DBCC CHECKTABLE statement for the Orders table, and specify the REPAIR_REBUILD option.C.Disconnect users from the Sales database. Execute the RESTORE DATABASE statement for the Sales databaseD.Execute the DBCC CLEANTABLE statement for the Orders table.E.Execute
12、the sp_table_validation stored procedure for the Orders table.解析：解析：Explanation: We should repair the database with the DBCC CHECKTABLE REPAIR_REBUILD command. We should run this repair statement when the database is configured to single user. Note: DBCC CHECKTABLE checks the integrity of the data,
13、index, text, ntext, and image pages for the specified table or indexed view. DBCC CHECKTABLE can take a specified repair option to repair the found errors but must be in single-user mode to use a repair option. It can specify the REBUILD_FAST option, which performs minor, non time-consuming repair a
14、ctions such as repairing extra keys in nonclustered indexes and can be done quickly and without risk of data loss; and it can also specify the REPAIR_REBUILD option, which performs all repairs that can done by REPAIR_FAST and as well as time-consuming repairs such as index rebuilding. These repairs
15、can also be done without risk of data loss. Incorrect Answers: B: The database option DBO use only, would only allow the database owner running the database. This might be too restrictive. C: We are not told when how often consistency checks are performed but assuming that consistency occurs more fr
16、equently than the database backups then using RESTORE DATABASE to restore a database from the last full backup would result in the loss of data entered into the database since the last full database backup was performed. This would result in data loss. D: DBCC CLEANTABLE is used to reclaim space aft
17、er a variable length column or a text column is dropped using the ALTER TABLE DROP COLUMN statement. E: The sp_table_validation stored procedure returns rowcount or checksum information on a table or indexed view, or compares the provided rowcount or checksum information with the specified table or
18、indexed view. This stored procedure is used in replication and checks that the structure of the table being replicated between two tables are identical, i.e., that the tables have the same columns existing in the same order, same data types and lengths, and same NULL/NOT NULL conditions.3.You are a
19、database administrator in the Los Angeles branch office of a specialty foods supplier. A mainframe database at the headquarters contains all company data. Each branch office contains a SQL Server 2000 computer that imports regional data from the mainframe database.The server in Los Angeles contains
20、a Data Transformation Services (DTS) package that uses OLE DB to connect to the companys mainframe database. The DTS package extracts and transforms data about buyers and products for that region. The DTS package then writes the data to the SQL Server database in Los Angeles.You need to configure a
21、SQL Server computer for the new branch office in Sydney. You want to copy the Los Angeles package and modify it so that it writes data to the SQL Server database in Sydney.You use the DTS Designer to modify the DTS package so that it imports regional data from the mainframe database to the server in
22、 Sydney. The modified DTS package runs successfully on the server in Los Angeles. You save this DTS package to the server in Sydney, but the server in Sydney cannot connect to the mainframe database.You want to enable the server in Sydney to connect to the mainframe database. What should you do?（分数：
23、2.00）A.Change the connection properties in the DTS package so that the package uses new login credentials to connect to the mainframe database.B.Modify the workflow in the DTS package so that the server in Sydney is included.C.On the server in Sydney, install an OLE DB provider for the mainframe dat
24、abase. D.On the server in Sydney, delete and then re-create the DTS package.解析：解析：Explanation: OLE DB is an API that allows COM applications to use data from OLE DB data sources, which includes data stored in different formats. An application uses an OLE DB provider to access an OLE DB data source.
25、An OLE DB provider is a COM component that accepts calls to the OLE DB API and performs the necessary processing required by request against the data source. In this scenario the OLE DB source is the companys mainframe database, which contains all company data. As each branch office contains a SQL S
26、erver 2000 computer that imports regional data from the mainframe database, all existing and future branch office SQL Server 2000 servers will require an OLE DB provider to access the companys mainframe database. Incorrect Answers: A: The DTS package requires an OLE DB provider to access an OLE DB d
27、ata source. It needs this provider to connect to the companys mainframe database. Resetting the connection properties in the DTS package so that the package uses new login credentials to connect to the mainframe database will not provide the DTS package with the required access to the mainframe data
28、base. B: A separate DTS package must be created for the Sydney branch office. This DTS package must extract data from the companys mainframe computer that is relevant to the branch office in Sydney. The existing DTS package used to extract data from the companys mainframe for the Los Angeles SQL Ser
29、ver 2000 server can be modified to serve the same function for the Sydney office. D: Re-creating the DTS package on the SQL Server 2000 server in the branch office in Sydney is unnecessary the that SQL Server 2000 server would still require an OLE DB provider to access the companys mainframe databas
30、e. It would require less administrative effort to modify the existing DTS package.4.You are the administrator of a SQL Server 2000 database named Articles. A large city newspaper uses this database to store its archived newspaper articles. Journalists query the database as part of their research. Yo
31、u have created full-text indexes so that users can query the articles quickly.A hardware failure forces you to restore the entire database from backup. After you restore the database, the journalists report that they can no longer run queries without receiving errors.You want to ensure that the jour
32、nalists can query the database. What should you do?（分数：2.00）A.Create a new full-text catalog for the Articles databaseB.Rebuild the full-text catalog for the Articles databaseC.Create a new full-text index on the table that holds the text for the articlesD.Repopulate the full-text index on the table
33、 that holds the text for the articles 解析：解析：Explanation: Backing up a database does not back up full-text index data in full-text catalogs. The full-text catalog files are not recovered during a Microsoft SQL Server recovery. However, if full-text indexes have been defined for tables, the metadata f
34、or the full-text index definitions are stored in the system tables in the database containing the full-text indexes. After a database backup is restored, the full-text index catalogs can be re-created and repopulated. Note 1: Setting up a full-text search is a three-step procedure: 1. Make sure that
35、 the full-text indexing feature has been installed with your SQL Server. The full-text index is installed as a service in the system. It would not have to be reinstalled after restoring a database. 2. Create a catalog in the operating system to store the full-text indexes. A catalog can contain the
36、indexes from one or more tables, and it is treated as a unit for maintenance purposes. In general, you shouldnt need more than one catalog per database. The catalog is saved outside the database on separate files 3. Before you can implement a full-text search in a given database, you must ensure tha
37、t the full-text search indexes are populated regularly. In this scenario we must recreate the index by repopulating the full-text catalog. Note 2: A SQL Server 2000 full-text index provides support for sophisticated word searches in character string data. The full-text index stores information about
38、 significant words and their location within a given column. This information is used to quickly complete full-text queries that search for rows with particular words or combinations of words. The full-text indexes are contained in full-text catalogs. Full-text catalogs and indexes are not stored in
39、 the database to which they belong. The catalogs and indexes are managed separately by the Microsoft Search service. Backing up a database does not back up full-text index data in full-text catalogs. However, if full-text indexes have been defined for tables, the meta data is backed up when a databa
40、se backup is created. Incorrect Answers: A: The catalog already exists. The catalog is stored outside SQL server and would not be affected by the failure and restoration of the database. B: Rebuildning the full text catalog will not enable the Journalist to run the full-text queries. The catalogs would have to be repopulated as well. C: The index should be repopulated since it already exists from the backup. There is no need to recreate it.
