sql server - Which Transact-SQL query is most efficient? -
plan pass exam "querying microsoft sql server 2012"
i have 1 question have problem understand.
question is:
which transact-sql query should use?
database contains table named purchases. thetable includes datetime column named purchasetime stores date , time each purchase made. there non-clustered index on purchasetime column. business team wants report displays total number of purchases madeon current day. need write query return correct results in efficient manner. transact-sql query should use?
possible answers are:
a.
select count(*) purchases purchasetime = convert(date, getdate())
b.
select count(*) purchases purchasetime = getdate()
c.
select count(*) purchases convert(varchar, purchasetime, 112) = convert(varchar, getdate(), 112)
d.
select count(*) purchases purchasetime >= convert(date, getdate()) , purchasetime < dateadd(day, 1, convert(date, getdate()))
this source: which transact-sql query should use?
according them correct answer 'd'. not see why more efficient 'a' ? in 'd' call 2 functions (convert , dateadd).
thanks help.
d efficient not converting datetime column other data type, means sql server can use indexes defined on purchasetime
column.
it known sargable expression
.
c ignore indexes defined on purchasetime
column , result in clustered scan if there 1 or table scan if heap (a table without clustered index).
and queries a , b not return correct results ignore records older when query executed.
Comments
Post a Comment