Home » Microsoft » 70-459 » What should you do?
You have a SQL Server 2012 database named Database1. You execute the following code:
You insert 3 million rows into Sales. You need to reduce the amount of time it takes to execute Proc1. What should you do?
A. Run the following: ALTER TABLE Sales ALTER COLUMN OrderDate datetime NOT NULL;
B. Change the WHERE clause to the following: WHERE OrderDate BETWEEN CAST(@date1,char(10)) AND CAST(@date2,char(10))
C. Remove the ORDER BY clause from the stored procedure.
D. Run the following:
DROP INDEX IX_Sales_OrderDate;
GO
CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
GO
Correct Answer: C
Explanation/Reference:
Thiago from Brazil - Aug 06 2013, 2:43 PM Report Spam
Exam A / Q39 -> is wrong
C is correct (Remove the ORDER BY clause from the stored procedure.)
-- BURGOS
I Desagree with previous answer (D) because it will force a lookup (by retiring "amount" coloumn) and proc will is uses NonSARG and will continue to perform a index scan anyway. Predicting a selective range of dates, to prevent Index Scan (in 3MM rows) is better change procedure (B).
In fact, I think that would be better the following steps:
-drop index and PK;
-change datatype of OrderDate to datetime;
-recreate PK as CLUSTERED;
-recreate index without ID in INCLUDE Clause (because ID is part of cluster now)
-recreate procedure without any "cast" function and without order by (because ID is natural order).
According to these references, the answer looks correct.
Reference:
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/good-practices-to-write-the-stored-procedures-in-sql-server/