October 6, 2015 at 12:23 am
Comments posted to this topic are about the item Querying Yesterday
October 6, 2015 at 6:39 am
Don't know anything about the temporal tables but your description reminds me of the automated change tracking tables. We built our own "history" tracking on those tables we felt needed it. It was more for an auditing need than anything else. We rarely use the information in them except perhaps if something got overwritten by mistake. But even then the data can be found in existing copies of the database. What the history does for me is provide a means to run a daily script that will highlight anomalies in the data. For example if a member's first name and birth date are both changed it is an indication that we may have an overwrite situation. Anyway perhaps temporal tables could help with identifying these out-of-character changes in data without the need for our own additional history tables.
October 6, 2015 at 7:07 am
This sounds similar in concept to Oracle's Flashback related features, which leverages the transaction log and are primarily intended for point-in-time recovery, but it allows for Flashback queries as well using "SELECT .. AS OF <TIMESTAMP> ..." syntax.
http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#i1009447
I can see SQL Server's Temporal Table being leveraged for performing surgical data recovery on individual tables, as opposed to performing database level point-in-time restores from the transaction log.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 6, 2015 at 7:20 am
Interesting stuff.
I have to ask though, why not just manually make this yourself with 2 tables and some triggers? All it's doing is tracking the history of the table into another table and adding additional syntax to go back into time to see the results of your data say, 6 months ago?
When I watched the video on this new feature, they didn't really touch on the subject of storage. This is basically copying the record through every change in the temporal table. For large tables, that's going to grow pretty large through each iteration of that record with a separate record and timestamp. The speaker kept saying things like, "Oh, nice! So, I don't have to worry about this table!" Kind of made my right eye twitch. 😎
I think some use cases for me is the slowly changing dimensions. Another would be for auditing the records through the cleaning process as I don't control the source data we are storing. So, being able to go back to the original raw record and track the iteration of that record during the transformation process could be useful too. I would have a copy of the original record in the flatfile that's compressed and archived after the importation, a copy of the cleaned record in the actual table and multiple copies of the original and cleaned record through the transformation process in the history table.
October 6, 2015 at 9:42 am
xsevensinzx (10/6/2015)
Interesting stuff.I have to ask though, why not just manually make this yourself with 2 tables and some triggers? All it's doing is tracking the history of the table into another table and adding additional syntax to go back into time to see the results of your data say, 6 months ago?
Agreed, I've created many systems where we had to manually create the tables and triggers to maintain history. But, now, with a temporal table, the question is: Why do it manually?
It's now just a quick change to the table. And you can add it to existing tables via the ALTER statement.
As to any storage questions, that is a function of if and why you need to track history from a business standpoint. Once you decided you do need history, than now you have two options: Temporal, or the manual approach.
Also, this is very different from CDC (change data capture).
The more you are prepared, the less you need it.
October 6, 2015 at 11:09 am
Given a case-use where the requirement is auditing, we can use triggers to capture only specific DML operations (ex: only updates and deletes but not initial inserts), and we can choose to audit only specific columns.
Do we have this same level of control for Temporal Tables?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 6, 2015 at 11:44 am
Andrew..Peterson (10/6/2015)
xsevensinzx (10/6/2015)
Interesting stuff.I have to ask though, why not just manually make this yourself with 2 tables and some triggers? All it's doing is tracking the history of the table into another table and adding additional syntax to go back into time to see the results of your data say, 6 months ago?
Agreed, I've created many systems where we had to manually create the tables and triggers to maintain history. But, now, with a temporal table, the question is: Why do it manually?
It's now just a quick change to the table. And you can add it to existing tables via the ALTER statement.
As to any storage questions, that is a function of if and why you need to track history from a business standpoint. Once you decided you do need history, than now you have two options: Temporal, or the manual approach.
Also, this is very different from CDC (change data capture).
Well, these are supposed to be features you don't have with a new version, not features we kind of already have 😛
But I totally get the benefit of accessibility and ease of use too.
October 6, 2015 at 2:53 pm
xsevensinzx (10/6/2015)
Interesting stuff.I have to ask though, why not just manually make this yourself with 2 tables and some triggers? All it's doing is tracking the history of the table into another table and adding additional syntax to go back into time to see the results of your data say, 6 months ago?
That's exactly what we did to a number of tables.
October 7, 2015 at 9:13 am
I'm not sure what you want with a feature like this. You don't have this now. You have to build it, and ensure it works, and debug it. There are certainly complexities in querying your own tables as well and joining data together.
This simplifies development, and it's a good addition to SQL Server. It's not groundbreaking, but what is? Window functions? We could do those before with complex T-SQL. Now they're simpler, and perform better. I suspect the same here.
November 10, 2015 at 1:56 am
Steve Jones - SSC Editor (10/7/2015)
I'm not sure what you want with a feature like this. You don't have this now. You have to build it, and ensure it works, and debug it. There are certainly complexities in querying your own tables as well and joining data together.This simplifies development, and it's a good addition to SQL Server. It's not groundbreaking, but what is? Window functions? We could do those before with complex T-SQL. Now they're simpler, and perform better. I suspect the same here.
Totally agree.
Also, for most including me, use of such features often occurs after the initial visionaries attempt to use it in anger. Often for purposes not originally foreseen.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 10, 2015 at 6:25 am
I don't recall all of the reasons/caveats offhand but I remember reading about it and then studying how it works. All I can remember is "OK then... there's a feature I'm not going to use. I'll stick to Type 2 SCDs, instead".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply