Querying Data In Temporal Tables

Jeanne Combrinck shows us how to query data stored in temporal tables:

When you want to get latest (actual) state of data in a temporal table, you can query the same way as you query a normal table. If the PERIOD columns are not hidden, their values will appear in a SELECT * query. If you specified PERIOD columns as hidden, their values won’t appear in a SELECT * query. When the PERIOD columns are hidden, reference the PERIOD columns specifically in the SELECT clause to return the values for these columns.

To perform any type of time-based analysis, use the new FOR SYSTEM_TIME clause with four temporal-specific sub-clauses to query data across the current and history tables.

  • AS OF <date_time>

  • FROM <start_date_time> TO <end_date_time>

  • BETWEEN <start_date_time> AND <end_date_time>

  • CONTAINED IN (<start_date_time> , <end_date_time>)

  • ALL

Do check it out.  The big downside I’ve seen to temporal tables is that I can’t query a temporal table as of a per-row timestamp in another table.

Related Posts

Time Travel in Snowflake

Koen Verbeeck shows an interesting feature in Snowflake: Time travel in Snowflake is similar to temporal tables in SQL Server: it allows you to query the history rows of a table. If you delete or update some rows, you can retrieve the status of the table at the point in time before you executed that […]

Read More

Error Messages Related to Temporal Tables

Mala Mahadevan digs into temporal tables: Last month I was fortunate to have my first ever article published on Simple-Talk, among the best quality website for sql server articles ever. During the process of writing this article I ran into several errors related to temporal tables that I have not seen before. Some of these are […]

Read More

Categories

July 2018
MTWTFSS
« Jun Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031