Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 32 discussion

Actual exam question from Microsoft's 70-761

Question #: 32
Topic #: 1

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the
Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:

Details for the Application.Cities table are shown in the following table:

Details for the Sales.CustomerCategories table are shown in the following table:

You discover an application bug that impacts customer data for records created on or after January 1, 2014. In order to fix the data impacted by the bug, application programmers require a report that contains customer data as it existed on December 31, 2013.
You need to provide the query for the report.
Which Transact-SQL statement should you use?

A.

B.

C.

D.

E.

Show Suggested Answer

Suggested Answer: D
The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The DATEFROMPARTS function returns a date value for the specified year, month, and day.
Incorrect Answers:
A: ValidFrom should be less (<) than @sdate AND ValidTo should be greater (>) than @edate.
B: We should add a day with DATEADD, not subtract one day.
C: We cannot compare a date to an exact datetime.
References:
https://msdn.microsoft.com/en-us/library/ms187819.aspx

by AshleyLiang at Aug. 24, 2019, 7:59 a.m.

Comments

Submit Cancel

safiullah

Highly Voted 5 years, 9 months ago

Correct answer is C, because you have system versioning enabled for all the tables. FOR SYSTEM_TIME with AS OF option because the question says, state AS OF @DATE. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

upvoted 10 times

M4x

5 years, 9 months ago

No. In AS OF it compare also the TIME portion. https://docs.microsoft.com/en-US/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-2017

upvoted 4 times

Backy

5 years ago

No, AS OF uses the type of @Date which is DATE, DATEFROMPARTS() includes time but implicit cast cuts it out and @Date is just DATE

upvoted 1 times

...

Tazul

Highly Voted 5 years, 9 months ago

Correct answer: C

upvoted 5 times

...

Billybob0604

Most Recent 4 years, 5 months ago

D must be wrong. Theory clearly tells that the answer should be C. Using the AS OF sub clause can return a state of the data for each row containing the values that were current at the specified time in the past. The AS OF sub clause returns all the records from the SQL Server system versioned and history table that satisfied the below criteria: StartTime <= SpecifiedTime AND EndTime > SpecifiedTime

upvoted 1 times

...

Vermonster

4 years, 5 months ago

I agree with FOR SYSTEM_TIME as of since it doesn't say what time and doesn't say that it has to be SQL SERVER 2012 (when the feature didn't exist)

upvoted 1 times

Vermonster

4 years, 5 months ago

Could also be a temporal table but we'd need to know the history table....

upvoted 1 times

...

BabyBee

4 years, 6 months ago

None of them work in my test, A returns data but as specifying a ValidTo date do not return the actual records but expired on ValidTo date. C with AS OF do not return anything without an specific DATETIME Value, using only a date value won't return anything. And the rest do not return anything at all.

upvoted 1 times

...

tcroots19

5 years, 3 months ago

TLDR...all seemed flawed, but C seems closed to me. thoughts? A - this is wrong b/c it gets sdate and edate backwards...so no results...even if worked, could have dupes if changed during window B - this seems close, but is going to miss anything changed on 12-30 and 12-31...same dupe issue as A C - Seems just about best, think it only misses 12-31...also no dupes D - Doesn't work b/c only queries against main table (e.g. anything changed in January doesn't show up) E - Contained in same dates should never work

upvoted 1 times

Backy

5 years ago

What's wrong with E? start and end date can be the same because CONTAINED IN assumes inclusive for start and end

upvoted 1 times

...

ChaosRedeemed

5 years, 7 months ago

Correct Answer is C A: incorrect because it specfies ValidTo so any data that has not been changed since before that date will not be included B: incorrect edate is set do the date before, should dateadd(d,1,@sdate) but still has the same issue as answer A C: Correct (If you are testing this yourself, be careful that you test AS OF @DATE, a day after you create you temporal table. If you dont the date wil not be valid for that day as the time portion is after the start of the day) D: incorrect there is no FOR SYSTEM TIME, so no historical data E: incorrect, same issue as A, we set an enddate so we will not include records with the edate set to '9999-12-31 23:59:59.9999999'

upvoted 4 times

...

imran

5 years, 8 months ago

I have tested it C SEEMS TO BE CORRECT

upvoted 3 times

...