In SQL Server, temporal tables allow you to keep a full history of changes to your data, enabling you to query both current and historical data easily. The FOR SYSTEM_TIME clause is used with the SELECT statement to specify a point in time or a range of time for which you want to retrieve historical records from a temporal table.
Querying Historical Data Using FOR SYSTEM_TIME
Here's how to use the FOR SYSTEM_TIME clause to query historical data:
1. Point in Time Query: To retrieve data as it was at a specific point in time.
SELECT *
FROM YourTemporalTable
FOR SYSTEM_TIME AS OF 'YYYY-MM-DD HH:MM:SS'
Replace YourTemporalTable with the name of your temporal table, and provide the specific date and time in the format YYYY-MM-DD HH:MM:SS.
2. Range of Time Query: To retrieve data that was valid during a specific time interval.
SELECT *
FROM YourTemporalTable
FOR SYSTEM_TIME BETWEEN 'StartDateTime' AND 'EndDateTime'
Replace StartDateTime and EndDateTime with your desired datetime values in the format YYYY-MM-DD HH:MM:SS.
3. All Historical Data: To retrieve all historical versions of the records.
SELECT *
FROM YourTemporalTable
FOR SYSTEM_TIME ALL
Example
Assume we have a temporal table named EmployeeHistory with historical records of employee data. Here's how you could query it:
1. Query the state of the table at a specific time:
SELECT *
FROM EmployeeHistory
FOR SYSTEM_TIME AS OF '2023-01-15 12:00:00'
2. Query the table for a specific date range:
SELECT *
FROM EmployeeHistory
FOR SYSTEM_TIME BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
3. Retrieve all historical records:
SELECT *
FROM EmployeeHistory
FOR SYSTEM_TIME ALL
Important Notes
- Ensure that your SQL Server version supports temporal tables; they were introduced in SQL Server 2016.
- The FOR SYSTEM_TIME clause can only be used with tables that have been defined as temporal tables.
- Be cautious with the datetime values ensuring they are in the correct format and represent actual points in your temporal history.
Using the FOR SYSTEM_TIME clause, you can effectively query and analyze the historical data stored in temporal tables in SQL Server.