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.
Option A
B.
Option B
C.
Option C
D.
Option D
Thank you for posting.It had been really fantastic reading about all of your opinion of this kind of topic area.I was seeking such a write up for awhile,and fortunately i came accross your blog.I hope you certainly will continue to keep helping people in the same way in future also.
http://www.surfworld.com
Wrong it should be C.
https://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable
Correct answer is A. The CONVERT operation is more effective (just one step: convert); the CAST requires two steps (conversion, and then comparison).
The answer should be A.
When the query is been prepared, it is a one-time action to convert the parameters @date1 and @date2.
But is would be a conversion for every row if answer C is used. Which will take longer in most cases.
The correct answer is A not because CONVERT works faster than CAST. If you want to gest index seek instead of index scan, you simply should not use any functions in the WHERE clause, see:
https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/