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

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -