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 91 discussion

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

HOTSPOT -
You have a table named HumanResources.Department that was created with the query shown in the exhibit. (Click the Exhibit button.)

You need to query temporal data in the table.
In the table below, identify the Transact-SQL segments that must be used to retrieve the appropriate data.
NOTE: Make only one selection in each column.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
CONTAINED IN: If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance.
Incorrect Answers:
Not ALL: Returns the union of rows that belong to the current and the history table.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table
by AshleyLiang at Aug. 23, 2019, 4:15 a.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
raf77
Highly Voted 5 years, 8 months ago
The answer is correct. Should be AS OF & CONTAINED IN. CONTAINED IN: Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included. Source: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017
upvoted 9 times
Hoglet
4 years, 5 months ago
You can still construct a query that will return the current "live" FOR SYSTEM_TIME BETWEEN '1900-01-01' AND '9999-12-31 23:59:59.99'
upvoted 1 times
...
...
Vermonster
Most Recent 4 years, 4 months ago
AS OF and CONTAINED IN - Contained IN only pulls from the history table
upvoted 1 times
...
mattia_88
5 years, 6 months ago
FROM / BETWEEN / CONTAINED retreive data from history and current table
upvoted 1 times
...
mattia_88
5 years, 6 months ago
FOR SYSTEM_TIME CONTAINED IN clause internally retrieving the data from both the Temporal Table and History Table...SEE THE EXECUTION PLAN
upvoted 1 times
BabyBee
4 years, 5 months ago
Unless you add a CHECK constrain to the table that verifies that the ValidTo column value is indeed the maximum possible value in the type. CHECK (validTO = '99991231 23:59:59.999'). Text from Book Exam Ref 70-761 Querying Data with Transact-SQL Book by Itzik Ben-Gan
upvoted 1 times
...
...
AshleyLiang
5 years, 8 months ago
If only historical rows are required, the recommendation is querying the history table directly, i.e. use FROM for the situation of the second column.
upvoted 1 times
tz_123
4 years, 10 months ago
The original answer is correct: if a row's SysStartTime in the current table is < the @endtime in a query using FOR SYSTEM_TIME FROM @starttime TO @endtime, then it will still be retrieved.
upvoted 1 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