1、微软认证 70-228模拟试卷 9及答案与解析 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 structure. The table properties are config
2、ured as shown in the exhibit. You correct all the negative numbers in the table. You must prevent the database from storing negative numbers. You also want to minimize use of server resources and physical I/O.Which transact-SQL statement should you execute? （ A） ALTER TABLE dbo.storagelocations ADD
3、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 OK_UnitsStored, StorageLocations, UnitsStored GO （ D） CREATE PROC UpdateUnitsStored (StorageLocationID int, Unit
4、sStored bigint)AS IF UnitsStored 0 DATSERROR (50099, 17) ELSE UPDATE storaheLocations SET UnitsStored = UnitsStored WHERE StorageLocationID = storageLocationID 2 You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. You perform full database backups eve
5、ry two days. 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 error
6、s while minimizing the amount of data lost. What should you do? （ 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. Ena
7、ble the DBO 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 database （ D） Execute the DBCC CLEANTABLE statement for the Ord
8、ers table. （ E） Execute the sp_table_validation stored procedure for the Orders table. 3 You are a 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
9、computer that imports regional data from the mainframe database. The server in Los Angeles contains 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
10、 DTS package then writes the data to the SQL Server database in Los Angeles. You need to configure a 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 t
11、o modify the DTS package so that it imports regional data from the mainframe database to the server in 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
12、. You want to enable the server in Sydney to connect to the mainframe database. What should you do? （ 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 se
13、rver in Sydney is included. （ C） On the server in Sydney, install an OLE DB provider for the mainframe database. （ D） On the server in Sydney, delete and then re-create the DTS package. 4 You are the administrator of a SQL Server 2000 database named Articles. A large city newspaper uses this databas
14、e to store its archived newspaper articles. Journalists query the database as part of their research. You 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 journal
15、ists report that they can no longer run queries without receiving errors. You want to ensure that the journalists can query the database. What should you do? （ A） Create a new full-text catalog for the Articles database （ B） Rebuild the full-text catalog for the Articles database （ C） Create a new f
16、ull-text index on the table that holds the text for the articles （ D） Repopulate the full-text index on the table that holds the text for the articles 微软认证 70-228模拟试卷 9答案与解析 1 【正确答案】 A 【试题解析】 Explanation: CHECK constraints are used to enforce domain integrity by restricting the values that are accep
17、ted 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 constraint is added in line 1 and the CHECK
18、 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. However, it is recommended that triggers only
19、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 does not address the insertion of negative va
20、lues in the existing table. D: This solution creates a stored procedure to perform the CHECK constraint. 2 【正确答案】 A 【试题解析】 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. N
21、ote: DBCC CHECKTABLE checks the integrity of the data, 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 op
22、tion, which performs minor, non time-consuming repair actions 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-con
23、suming repairs such as index rebuilding. These repairs 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
24、 performed but assuming that consistency occurs more frequently 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 dat
25、a loss. D: DBCC CLEANTABLE is used to reclaim space after 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 rowcou
26、nt or checksum information with the specified table or 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
27、lengths, and same NULL/NOT NULL conditions. 3 【正确答案】 C 【试题解析】 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. An OLE DB provider i
28、s 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 Server 2000 computer
29、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 data source. It needs
30、 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 database. B: A separate
31、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 Server 2000 server can
32、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 database. It would require
33、less administrative effort to modify the existing DTS package. 4 【正确答案】 D 【试题解析】 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 be
34、en defined for tables, the metadata for 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 th
35、ree-step procedure: 1. Make sure that 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-tex
36、t indexes. A catalog can contain the 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
37、a given database, you must ensure that 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 ful
38、l-text index stores information about 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 c
39、atalogs and indexes are not stored in 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
40、 meta data is backed up when a database 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.
