ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 55 discussion

Actual exam question from Microsoft's 70-761
Question #: 55
Topic #: 1
[All 70-761 Questions]

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You create a table by running the following Transact-SQL statement:

You need to return normalized data for all customers that were added in the year 2014.
Which Transact-SQL statement should you run?

A.

B.

C.

D.

E.

F.

G.

H.

Show Suggested Answer Hide Answer
Suggested Answer: G
The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of
2014 and 1st January 2015 (including the upper boundary):

SELECT * FROM Employee -

FOR SYSTEM_TIME -
BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000ORDER BY ValidFrom;
References:
https://msdn.microsoft.com/en-us/library/dn935015.aspx
by exam_taker5 at July 30, 2019, 7:27 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Backy
Highly Voted 4 years, 7 months ago
Answer is H "customers that were added in the year 2014" refers to the column DateCreated and it has nothing to do with the system column ValidFrom. Answer G will return more than the question asks for
upvoted 5 times
...
Vermonster
Most Recent 4 years, 4 months ago
Can't be H because the data type is wrong, can't be G because that will show all records touched in that timeframe, not just ones created. Therefore, I would go with XML PATH
upvoted 1 times
...
applepie
4 years, 4 months ago
The answer should be E since it needs a normalized data. G is wrong because it was asking for data created in 2014, DataCreated column should be used instead of SYSTEM_TIME. H is wrong because the result is not normalized. H will return multiple records for the same clientID etc of different validFrom and ValidTo.
upvoted 1 times
...
Andy7622
4 years, 4 months ago
We need customers added , historical data may shows changes to customers that were added earlier than in 2014 . So in my opinion it's H.
upvoted 1 times
...
Hoglet
4 years, 5 months ago
There is no good answer here H suffers for 2 reasons. 1) We aren't looking at when the rows were created, rather created or modified. We could create a Customer in 2013, then update the address is 2014. 2)The BETWEEN will include anything update at midnight 2015-01-01 G suffers as it will not include anything that occurred on the 2014-12-31 The PIVOT answer has potential, except the YEAR function isn't used and the PIVOT is incorrectly formed. Something to check when doing the exam
upvoted 1 times
...
Jorax
4 years, 6 months ago
If you create customer and set DateCreated explicitely to 2015-01-01 00:00:00.000000 it will be visible in G. If you use FOR SYSTEM TIME - you actually query temporal and historical tables - you get redundant data so its no longer normalized data. Probably H misses some casting (i didnt check that though) but still it fits more to the requirements than G.
upvoted 2 times
...
eduardogtc
4 years, 7 months ago
I don't think we can use FOR SYSTEM TIME here. The question is about data created and not data active. The G option, for example, could show data created before 2014. The closest option would be H, but there isn't any option correct in my opinion.
upvoted 3 times
...
Kiruu23
4 years, 8 months ago
I think the correct ans is E coz, in G option System_Time Between option is used. Using BETWEEN '2014-01-01' AND '2015-01-01' will include data for '2015-01-01 00:00:00.0000000' as well.
upvoted 1 times
...
imsun93
4 years, 8 months ago
answer is G but can we use FROM.. TO instead of BETWEEN.. AND?
upvoted 1 times
...
Ilray
4 years, 11 months ago
If row was inserted in 2014 and then deleted the H don't show this row - only G.
upvoted 2 times
...
vermeilyn
4 years, 11 months ago
G has flaw. If you want to use SYSTEM_TIME, SYSTEM_TIME AS OF '2015-01-01' is the correct one. Using BETWEEN .. AND '2015-01-01' will include data that was created at '2015-01-01 00:00:00.0000000'.
upvoted 1 times
vermeilyn
4 years, 11 months ago
Nvm, I think G is right.
upvoted 2 times
...
...
exam_taker5
5 years, 9 months ago
The explanation does not fit the question... I believe the answer is G, but I'm not positive.
upvoted 2 times
exam_taker5
5 years, 9 months ago
**I believe the answer is H
upvoted 14 times
AshleyLiang
5 years, 8 months ago
Agree because normalized data is required here.
upvoted 2 times
...
M4x
5 years, 8 months ago
You are wrong! The field is datetime2 NOT date, '20141231' is expanded to '2014-12-31 00:00:00.0000000' and you lost ALL records created between 00:00:00.0000001 and 23:59:59.9999999'
upvoted 10 times
Tazul
5 years, 7 months ago
I think, H is the closest answer. To be correct, we need to cast DateCreated column to Date. Where cast(DateCreated as Date) Between '20140101' and '20141231'
upvoted 3 times
AK80
4 years, 9 months ago
I believe H is wrong since it does not include results from the customerHistory table
upvoted 2 times
...
...
...
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago