首页
外语
计算机
考研
公务员
职业资格
财经
工程
司法
医学
专升本
自考
实用职业技能
登录
计算机
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
53
问题
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
微软认证
相关试题推荐
AlmosteveryyearsincetheendofthefinancialcrisishasstartedwithrosyexpectationsamongAmericanforecasters,andthis
Writeanessayof160-200wordsbasedonthefollowingdrawing.Inyouressay,youshould1)describethedrawingbriefly,
Thepicturedepictsafunnysceneinthehospitalvividly.Severalpeoplearewaitinginquitealongqueueoutsidetheclinicw
BarackObamaspentmuchtimeonthecampaigntrailproposingadramaticvisiontochangenotonlytheUnitedStatesforthebett
In2016,manyshoppersoptedtoavoidthefreneticcrowdsanddotheirholidayshoppingfromthecomfortoftheircomputer.But
LastThursday,theFrenchSenatepassedadigitalservicestax,whichwouldimposeanentirelynewtaxonlargemultinationalst
Thehumanbraincontains10thousandmillioncellsandeachofthesemayhaveathousandconnections.Suchenormousnumbersused
InParagraphs1and2,thetextshowsPTK’s______.Skepticalobserversbelievethatancestrytestingfailsto______.
NextmonthBritonswillhaveyetmoresmartphonestochoosefrom,whendevicesfromWiko,atwo-year-oldFrenchcompany,goons
NextmonthBritonswillhaveyetmoresmartphonestochoosefrom,whendevicesfromWiko,atwo-year-oldFrenchcompany,goons
随机试题
一台标注为“PentiumIII/600/128MB”的微型计算机,其中的“600”表示机器的()
函数f(x)=3x—x2的极值点是x=3。()
患者实感天旋地转,恶心欲吐,站立不稳,跌倒在地,神志清楚,数分钟后自行缓解。应诊断为()(2005年第70题)
机器单采浓缩白细胞悬液保存最佳温度为
A、宿主抗移植物反应B、急性排斥反应C、慢性排斥反应D、超急性排斥反应E、移植物抗宿主反应受者体液循环中若存在抗供者的抗体,常出现
某工程项目总价值1000万元,合同工期为18个月,现承包人因建设条件发生变化需增加额外工程费用50万元,则承包方提出工期索赔为()个月。
总分类账户与明细分类账户反映的经济内容相同,所提供会计信息的详略程度也相同。()
国际上对资产评估报告的分类一般包括()。
Therelationshipbetweenformaleducationandeconomicgrowthinpoorcountriesiswidelymisunderstoodbyeconomistsandpoliti
Someanimalbehavioristsarguethatcertainanimalscanrememberpastevents, anticipatefutureones,makeplansandcho
最新回复
(
0
)