Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 172 discussion

Actual exam question from Microsoft's 70-761

Question #: 172
Topic #: 1

You have a table named Table1 that contains 200 million rows. Table1 contains a column named SaleDate that has a data type of DateTime2(3).
Users report that the following query runs slowly.

You need to reduce the amount of time it takes to run the query.
What should you use to replace the WHERE statement?

A. WHERE SaleDate >= '2017-01-01' AND SaleDate < '2018-01-01'
B. WHERE cast(SaleDate as varchar(10)) BETWEEN '2017-01-01' AND '2017-12-31'
C. WHERE cast(SaleDate as date) BETWEEN '2017-01-01' AND '2017-12-31'
D. WHERE 2017 = year(SaleDate)

Show Suggested Answer

Suggested Answer: C 🗳️
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017

by AshleyLiang at Aug. 24, 2019, 1:14 p.m.

Comments

Submit Cancel

AshleyLiang

Highly Voted 5 years, 10 months ago

Correct answer is A.

upvoted 36 times

Prides

5 years, 4 months ago

ANSWER C actually will not include the date of 12-31-2017

upvoted 2 times

Max34

4 years, 10 months ago

Correct Answer is C. According to the book "Contained in" excludes 12--31-2017, but "Between" would include the date 12-31-2017.

upvoted 1 times

...

gtc108

Highly Voted 5 years, 4 months ago

Answer is A: In order for a query to be saragable, it should not use a function. https://www.sqlshack.com/how-to-use-sargable-expressions-in-t-sql-queries-performance-advantages-and-examples/

upvoted 13 times

...

Billybob0604

Most Recent 4 years, 5 months ago

100% sure A. if you want the query to run faster never use functions in the where column.

upvoted 1 times

...

Vermonster

4 years, 5 months ago

Definitely A - need to get function off search argument to optimize

upvoted 1 times

sssshhhh

4 years, 5 months ago

exactly, when you cast it in the WHERE clause the optimizer (mostly) can't and won't rely on the index anymore thus it just assumes the values have changed. This makes it perform index scans (slower) instead of index seeks (faster).

upvoted 1 times

...

sunz_1

4 years, 9 months ago

C defaults to 12-31 00:00 AM

upvoted 1 times

...

TheDUdeu

4 years, 9 months ago

The correct answer is A. How does any of the other solutions optimize better? gtc108 is right SARGABLE. Sure one answer might run correctly but this site has a lot of errors.

upvoted 2 times

...

arindam_pal

4 years, 11 months ago

The correct answer is B and tested in MS SQL 2019. The A and C both will fail due to datetime2 to varchar implicit conversion. Answer B is casting the datetime to varchar which will return the records.

upvoted 1 times

Backy

4 years, 11 months ago

Answer B does not make any sense. B may work to get correct result, but not in the context of question which asks for optimization. It is illogical to run CAST(SaleDate as varchar(10)) because such cast is a total waste of time, the result will have to be cast to DATE because the comparison used in BETWEEN is totally meaningless for VARCHAR. So you run an explicit CAST which hqs to be erased by implicit CAST. The only answers that make any sense are A and C, both work, the only issue is which one is faster for 200 million rows

upvoted 1 times

...

Backy

5 years ago

Both answers A and C are correct. The question should ask for two answers and not one. Answer C has an explicit cast, answer A does the same cast anyway although implicitly

upvoted 2 times

...

ertanasan

5 years, 2 months ago

Just created a table and tested the aggregate with the where clause in both A and C. They worked perfectly fast on 5M records. I think the most meaningful answer is A. Because C uses a function to cast which consumes extra time...

upvoted 3 times

...

Hoey

5 years, 2 months ago

I think A is wrong because as datetime2 we have to give time also. C is correct because Between And in SQL use closed interval.

upvoted 2 times

...

Jiacheng

5 years, 4 months ago

you could check with your SSMS, all A,B,C could work. And gtc108 is correct, without function makes perform best

upvoted 4 times

...

Lukis92

5 years, 5 months ago

The correct answer is C. SQL Server implicitly convert varchar on the right side to date type.

upvoted 1 times

...

New_user

5 years, 6 months ago

Answer A has type mismatch - SaleDate is DateTime2 type column, condition is varchar. There's same misteke in C. B is correct

upvoted 2 times

Andy7622

4 years, 6 months ago

'A' works strings implicitly converts into datetime because datetime has higher precedence level.

upvoted 1 times

...