How to Use Temporal Tables in SQL Server 2016
Vložit
- čas přidán 22. 05. 2017
- Have you ever needed to look at what data in a table used to look like?
If you have, it probably took a knuckle-cracking filled session of writing group-by statements, nested sub-queries, and window functions to write your time-travelling query.
Fortunately for us, SQL Server 2016 introduces a new feature to make our point-in-time analysis queries easy to write: temporal tables.
Full blog post: bertwagner.com/2017/07/19/how...
Want to receive the latest weekly blog posts and videos in your inbox? Sign up for the newsletter here: upscri.be/c77fc8/
Elsewhere on the internet:
bertwagner.com
/ bertwagner - Věda a technologie
Thank you very much! I've just discovered your channel, you're doing great job!
Brilliant work! Keep it up!
Thank you, it is exactly the explanation i needed it. You are brilliant!
point to point explanation
Thanks much, excellento!!! So clear!!!! I was initially totally confused after other docs
Excellent demo. Thank you.
Very clearly explained. Thanks Bert!
Amazing and superb video , easy to understand and follow
Thank you kind sir, for the free lessons!
Clearly explained Bert. Thank you.
Great feature, brilliant explanation.
Love it, clearly explained. Thank you
Thanks for the video.
Great explanation, thank you!
Excellent video!
Super easy explanation.
Nice simple and sweet explanation
Man, you as always are very cool. Love you :)
Excellent !
Very nice demo! It's clear! (2022)
Good explanation.
Nicely explained
Hey Wagner,
could you please share a video on adding new column to temporal table and accordingly in history table without loss of information or dropping tables.
Thanks
this video is amazing, i am new for the sql server and i kind of confused between temporal table and change data capture (CDC) . if you can answer please. thanks
Hi Bert, I've done a.lot of work with nhibernate managed temporal tables and I'm having problems understanding the SQL version based on videos I've seen. Should they not have two sets of from/to dates? The system/audit date and the user specified effective from/to dates? All the work I've done has used the user specified dates and has involved joins on multiple tables. Is there built in functionality to perform temporal table joins? I have my own methods which are not too complicated however it's a 2-3 step process that also involves recursive CTEs to date consolidate the result set. I'm wondering if SQL server managed temporal tables have a built in mechanism to perform temporal joins and return the right results for the right dates and have some value(s) change for every date change. I'm not talking about a point in time select (those are easy) but selects with joins for multiple tables' full history for example. I don't mean the history table when I say history. I don't usually care about the history table as I'm not interested in system dates. I'm interested in user specified effective date ranges. Thanks.
Thanks
That was a very good Video! I understand it finaly.
But I have one Question:
When you Query the history with the Time, you have the entry of the "current" Table and that entry from the historical Table. Why are you not seeing the historical entry only?
Is it because the "current" entrys SysEndDate is in the year 9999, so it is in the same Timeperiod as the historical one?
I have the need to introduce tracking of changes for a table of products and temporal tables seems to be the easiest solution, with one exception that I can't seem to find a solution for. I want the history table to be in its own database - the historical data is going to grow rapidly and by definition is unchanging, I don't want this to bloat the main database backup or AG replication! However the syntax to set up the versioning enforces "two part naming convention" and also refuses to work with a synonym or a view. Is there any other possible workaround?
Is there a way to add column to know if it’s insert/update/delete ?
Hello Bert, Thanks for this! It is something that I can use for an application that I am developing. I'm having trouble processing "updates" to the table through an MS Access front-end (-7776 error). I think this is mainly because of some ODBC driver issues. I have no trouble adding records. I am assuming that through any other front-end such as a web app, this update shouldn't be an issue, because otherwise the whole premise of having these temporal tables, fail!. Could you please confirm if you have had any issues through the front-end applications you have used to get the temporal tables to do their thing?
I solved this by including a column with the data type ROWVERSION
after load, the first update did not carry the mileage? second update carried though....
How can you show the running script in the result (Message) windows?. Actually 2 years before "Luigi Zambetti" asked this question in the comment box. But you gave different answer.
Hi how to delete the data from temporal table , i want to delete the old data from history table , can you help me?
How can you show the script in the result windows?
When you run something (F5), in the Message windows (below) appears the script that you have runned.
Excellent work and very easy to understand.. Thanks. I want to ask that if am trying the same thing in my SQL 2016 so I am getting errors on SYSTEM_VERSIONING, GENERATED and HISTORY_TABLE. When I am trying to run your script then I am getting error like this:
Incorrect syntax near 'SYSTEM_VERSIONING'.
Incorrect syntax near 'GENERATED'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
What should I do? Please suggest
Just a heads-up you can't replicate(snapshot/transactional) a table that has SYSTEM_VERSIONING = ON.
Thanks for sharing. But dude u r sweating.
Am I the only one annoyed by the butchered pronunciation of "temporal"? It's TEM-poral, not tem-POR-al.
Nope
Lol always got a grammar nazi under every video
And gross!