2. 计算机
  3. You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configu

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configu

admin2009-05-19  57
问题 You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configured as shown in the exhibit.


Users frequently need details about parts. Users search for data by using the following query.

SELECT       Parts.SKU, Locations.Description, Manufacturer.Name,
              PartsLocations.Qty, PartsLocations.LastInventoried
FROM         Parts
INNER JOIN   Manufacturer ON Parts.ManufacturerID=
               Manufacturer.ManufacturerID
INNER JOIN    PartsLocation ON Parts.PartID = Parts.Location.PartsID
INNER JOIN    Locations ON Parts.LocationID= Locations.LocationID
WHERE  SKU?

You need to accelerate query response time. What should you do?
选项 A、Create a parameterised stored procedure to retrieve the data.
B、Create a denormalized table that is maintained by triggers.
C、Use ad hoc queries to retrieve the data.
D、Create a nonclustered index on the primary key of each table.
答案D
解析 Explanation: A nonclustered index is similar to an index in a textbook. The data is stored in one place, the index in another, with pointers to the storage location of the data. The index contains entries describing the exact location in the table of the data values being searched for in the queries and should be used for columns that contain a large number of distinct values; queries that do not return large result sets; columns frequently involved in search conditions of a query that return exact matches; and decision-support-system applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

Note: A better solution, not listed here, would to create clustered indexes on the primary keys and nonclustered indexes on the foreign keys.

Incorrect Answers:
A: A parameterized query would not improve performance.
Parameterized queries are SQL queries written for reusability. They contain parameter markers as placeholders for data that will change from execution to execution. In the Data Transformation Services (DTS) tasks that use parameterized queries.

B: Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables. In this scenario four tables are being joined in the query. The normalization here is thus permissible.

Note: Database normalization often improves query performance. When useful indexes are available, the SQL Server 2000 query optimizer can select rapid, efficient joins between tables.

Normalization is advantageous as it aids faster sorting and index creation; enables a larger number of clustered indexes; allows for narrower and more compact indexes; reduces the number of indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements; and reduces null values and the opportunity for inconsistency, which increase database compactness.

However, as normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.

C: An ad hoc query is a query that is composed for a particular task. The code in this scenario is an example of an ad hoc query.
转载请注明原文地址:https://www.kaotiyun.com/show/GuhZ777K
本试题收录于： 微软70228题库微软认证分类
0

微软70228

微软认证

相关试题推荐
随机试题
最新回复(0)