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.
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?
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
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?
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.
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
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?
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
Thank you very much! I've just discovered your channel, you're doing great job!
Thank you, it is exactly the explanation i needed it. You are brilliant!
Thanks much, excellento!!! So clear!!!! I was initially totally confused after other docs
Amazing and superb video , easy to understand and follow
Thank you kind sir, for the free lessons!
Clearly explained Bert. Thank you.
Nice simple and sweet explanation
Love it, clearly explained. Thank you
Very clearly explained. Thanks Bert!
Great feature, brilliant explanation.
Brilliant work! Keep it up!
point to point explanation
Super easy explanation.
Great explanation, thank you!
Excellent demo. Thank you.
after load, the first update did not carry the mileage? second update carried though....
Thanks for the video.
Good explanation.
Man, you as always are very cool. Love you :)
Very nice demo! It's clear! (2022)
Nicely explained
Is there a way to add column to know if it’s insert/update/delete ?
Excellent video!
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.
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?
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
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
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.
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
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?
Hi how to delete the data from temporal table , i want to delete the old data from history table , can you help me?
Excellent !
Just a heads-up you can't replicate(snapshot/transactional) a table that has SYSTEM_VERSIONING = ON.
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.
Thanks
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
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
Thanks for sharing. But dude u r sweating.
And gross!