首页
外语
计算机
考研
公务员
职业资格
财经
工程
司法
医学
专升本
自考
实用职业技能
登录
计算机
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to
admin
2009-05-19
76
问题
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to facilitate a large import of data, and rebuild them after the import is complete.
Users report that response times from a stored procedure have become slower. In the stored procedure, you examine the following code.
CREATE PROCEDURE GetCustomer
( @CustomerID Int )
AS
SELECT FirstName, LastName, Address1, Address2, City,
State, Zip, MAX(PurchaseDate) AS LastPurchase
FROM Customers C
JOIN Addresses A ON A.AddressID = C.AddressID
JOIN Purchases P ON P.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip
You want to improve the performance of the procedure by using the least amount of administrative effort. What should you do?
选项
A、Recompile the stored procedure.
B、Update the database statistics.
C、Check the consistency of the database and repair errors.
D、Create a view and modify the stored procedure to use the view.
答案
A
解析
Explanation: By recompiling the stored procedure SQL Server will create a new execution plan for the statements in the procedure. SQL Server will detect that statistics are not up to date and it will automatically recreate the statistics during this process. The execution plan will be optimized for the current distribution of data in the tables.
Note: Stored procedures can be used to improve database query performance. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan that is cached on the server. When the database is changed by the addition of new indexes or when data in indexed columns are changed, the original query plans and stored procedures used to access the tables should be reoptimized by recompiling them.
This optimization happens automatically the first time a stored procedure is run after SQL Server 2000 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically occur until the next time the stored procedure is run after SQL Server is restarted. The stored procedure can be manually recompiled by using the sp_recompile system stored procedure. This forces the stored procedure to be recompiled the next time it is run.
Incorrect Answers:
B: SQL Server detects whether statistics are not up-to-date, and by default it automatically updates them during query optimization. By recompiling the query the statistics will automatically be updated.
Note: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Query optimization depends on the accuracy of the distribution steps. If there is significant change in the key values in the index, or if a large amount of data in an indexed column has been added, changed, or removed, or the table has been truncated and then repopulated, UPDATE STATISTICS should be rerun.
C: Consistency of a database is maintained through the use of constraints and triggers. These
mechanisms are used to ensure that the database remains consistent by controlling the type of data that can be inserted into a column, through referential integrity checks, and through cascading referential constraints.
D: A view is a virtual table that can be used to restrict users access to the base table, or to join various base tables so that they appear as a single table. However, creating a view that the stored procedure must access would require the creation of indexes on the view and will have to be updated when the base tables are updates. This would require greater administrative effort.
转载请注明原文地址:https://kaotiyun.com/show/RuhZ777K
本试题收录于:
微软70228题库微软认证分类
0
微软70228
微软认证
相关试题推荐
Itishardtomakemoneypeddlingsocialmediaanywhere.Duringtheirfirstfewyearsinbusiness,FacebookandTwitterlostpo
CigarettesmokingisahealthhazardofsufficientimportanceintheUnitedStatestowarrantappropriateremedialaction.Itwa
EricHansenwritesabouttravelasaparticipatingenthusiastratherthanamereobserver.【T1】Itgivesthesenineessays,based
In2016,manyshoppersoptedtoavoidthefreneticcrowdsanddotheirholidayshoppingfromthecomfortoftheircomputer.But
LastThursday,theFrenchSenatepassedadigitalservicestax,whichwouldimposeanentirelynewtaxonlargemultinationalst
Thehumanbraincontains10thousandmillioncellsandeachofthesemayhaveathousandconnections.Suchenormousnumbersused
Evenintraditionaloffices,"thelinguafrancaofcorporateAmericahasgottenmuchmoreemotionalandmuchmoreright-brained
Notlongago,chiefexecutiveofficers(CEOS)werelandedasheroes,leadersofthebraveneweconomy.BillGatesandJackWelchw
InToday’smanagement-theoryindustry,reputation—oratleastthecorporatekind—isa"strategicasset"thatcanbe"leveraged"
Thefollowingscenariowillbeusedforquestions29and30.Johnisanetworkadministratorandhasbeentoldbyoneofhisnet
随机试题
与关系型对手谈判的禁忌不包括()
ThelettersPTO______parent-teacherorganization.
A.氟氯烷烃B.丙二醇C.PVPD.枸橼酸钠E.PVA气雾剂中的潜溶剂
男,56岁。颈肩痛1个月,并向右手放射,右手拇指痛觉减弱,肱二头肌肌力弱。初步诊断是
【背景资料】某公司承建的市政道路工程,长2km,与现况道路正交,合同工期为2015年6月1日至8月31日。道路路面底基层设计为300mm水泥稳定土;道路下方设计有一条DN1200mm钢筋混凝土雨水管道,该管道在道路交叉口处与现状道路下的现有DN300mm
单位内部会计监督的主体是各单位的单位负责人。()
《INCOTERMS2010》中,买方自费办理货物出口结关手续的术语是()。
第三次技术革命开始于20世纪40年代的美国,其主要标志是()。
ThemodernEnglishbeganinthe______century.
A、Eatinginacafeteria.B、Buyingsomethinginastore.C、Talkingonthetelephone.D、Gettingmoneyatabank.C综合理解题。男士先问,你打通了
最新回复
(
0
)