Back last June, I started this series on temporal tables, and in my head, I had enough information to make an educated set of examples on how to use them. In the back of my mind though, I knew that I hadn’t quite thought enough about the whole process, particularly when you have several rows (or as I will note in the next blog entry, tables) that you are going to work with as a unit.<\/p>\n
So in this blog, I want to look a the mechanics of how multiple operations in the same transaction behave when we are in a single table. Then in the next entry to the series, I will take it to the logical conclusion of how we manage things when we have an “object” (like a salesOrder and salesOrderLineItem) that need to be bundled together.<\/p>\n
This is executing in RC3: <\/p>\n
select @@version<\/p>\n
————————————————————————————————————————–
\nMicrosoft SQL Server 2016 (RC3) – 13.0.1400.361 (X64)
\n\u00a0\u00a0\u00a0 Apr\u00a0 9 2016 01:59:22
\n\u00a0\u00a0\u00a0 Copyright (c) Microsoft Corporation
\n\u00a0\u00a0\u00a0 Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)<\/p>\n
First, create a table. Note that you don’t have to use the name SysStartTime or SysEndTime for the time period columns, which is the typical name because they use is BOL, but they used a different name in the introductory example. So I used a name more like my personal naming standards. I also noticed that the datetime2 column was not a datetime2(7, but rather was a (2). Later in this series, I will try out the different granularities to show what meaning they have for our queries as well. <\/p>\n
CREATE SCHEMA Sales
\nGO<\/p>\n
CREATE TABLE Sales.SalesOrder
\n(\u00a0
\n\u00a0\u00a0\u00a0 SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
\n\u00a0\u00a0\u00a0 Data varchar(30) NOT NULL,\u00a0\u00a0\u00a0 –just the piece of data I will be changing
\n\u00a0\u00a0\u00a0 ValidStartTime datetime2 (7) GENERATED ALWAYS AS ROW START,
\n\u00a0\u00a0\u00a0 ValidEndTime datetime2 (7) GENERATED ALWAYS AS ROW END,
\n\u00a0\u00a0\u00a0 PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)
\n)\u00a0
\nWITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Sales.SalesOrderHistory)); –Another future thought, put in a different schema? I think not, but, it intrigues me nonetheless.
\nGO<\/p>\n
Scenario 1: Single row, in a transaction create a row, update it a few times<\/strong><\/p>\n Note: I won’t demo rolling back, as it will be have exactly as expected.<\/p>\n First, start a transaction, and insert some data.<\/p>\n BEGIN TRANSACTION Looking at the data, we see: This returns:<\/p>\n SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime No version history yet, naturally, just the data as we created it. Now, still in the transaction, update the row.<\/p>\n –SELECT @@TRANCOUNT; –Check if you need to make sure! \nUPDATE Sales.SalesOrder Then we check the data:<\/p>\n SELECT * You can see a version has been created, but notice the start and end times are exactly the same: SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime Also note that the start and end time on the base SalesOrder row has not changed either. Lets update the row again: SELECT * Still no changes to the timestamp. But we keep accumulating changes:<\/p>\n SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime Now we have completed what we are wanting, so we commit:<\/p>\n COMMIT<\/p>\n Checking the results, effectively, since the versions never really existed, you cannot see them using the syntax in the FROM clause as you can see using the AS OF time of the start timestamp, which would be the only time that even somewhat looks like it might return the history:<\/p>\n SELECT * This returns:<\/p>\n SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SysStartTime >= PassedValue > SysEndTime (where SysStartTime and SysEndTime corresponds to the names you chose)<\/p>\n<\/blockquote>\n So the changed rows are never seen unless you query the history table.<\/p>\n Scenario 2: Create two rows slightly apart. Want to make sure the start time is different for the rows. Start a transaction, update them both at different times.<\/strong><\/p>\n The thing here is that we want to see how you use the fact that the StartTime values are synchronized with the transaction. If you have more than one row that need to be treated as a group, ideally you modify them in a transaction already. If for no other reason than rolling back the previous operations in the last modification fails. Here though, we are starting to think temporally. If you don\u2019t synchronize your timestamps, you are apt to get illogical results at times.\u00a0 I will show this more in Part 4 when I have two tables with control values that need to match (denormalizations are another typical concern. If you expect column1 to match column2, and they are updated in two statements, the time gap in the temporal progression could give you weird results if you hit it just right.)<\/p>\n First, let\u2019s do this without a transaction<\/p>\n INSERT INTO\u00a0 Sales.SalesOrder (SalesOrderID, Data) WAITFOR DELAY ’00:00:01′ –slowing down time makes showing concurrency problems easier so you can hit the gaps easier<\/p>\n INSERT INTO\u00a0 Sales.SalesOrder (SalesOrderID, Data) and then, verify you are in a consistent state that you expect:<\/p>\n SELECT * This returns:<\/p>\n SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime Note that the start time is bit over a second different. Now start a transaction, update both rows in a way that shows the time they were changed. Wait a moment between updates, either manually, or using waitfor, as I have, but this time use a transaction:<\/p>\n BEGIN TRANSACTION;<\/p>\n UPDATE Sales.SalesOrder WAITFOR DELAY ’00:00:02′<\/p>\n UPDATE Sales.SalesOrder Now, look at the state of the table as you can see it:<\/p>\n SELECT * The start times are synchronized now for the two rows, so the view of 2 and 3 will be consistent for this change, if not the insert:<\/p>\n SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime The start time of row 2 is exactly the time you put into the Data column for row 2 (the first row) for both rows. That would effectively be the time the transaction started.<\/p>\n Add one more row, with the SYSDATETIME() value for the Data column, which will let you see when the row was created:<\/p>\n INSERT INTO\u00a0 Sales.SalesOrder (SalesOrderID, Data) SELECT * The new row has the same ValidStartTime value as the other modified rows.<\/p>\n SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime Commit the changes:<\/p>\n COMMIT<\/p>\n This is actually really great, because you can effectively update, modify, and delete all rows at the same time as far as the temporal history will reflect notice. I will use this in the next blog entry to deal with logical object level changes (SalesOrder and SalesOrderLineItems).<\/p>\n Scenario 3: Single row, created, updated a few times, deleted.<\/strong><\/p>\n To show the final thing that will occur. I will, in a transaction, make a new row, change it and toss it aside. You would think the row never really existed, and you would wrong really.\u00a0 It would have log entries, it might even have been used to create other data. As such, it will still have history, which could be useful in some scenario I haven’t yet figured out! (If you roll back the transaction, it really would have never existed).<\/p>\n BEGIN TRANSACTION<\/p>\n INSERT INTO\u00a0 Sales.SalesOrder (SalesOrderID, Data) UPDATE Sales.SalesOrder UPDATE Sales.SalesOrder DELETE Sales.SalesOrder COMMIT<\/p>\n Look at the data<\/p>\n SELECT * SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime But in the history:<\/p>\n SELECT * SalesOrderId Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidStartTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValidEndTime Everything we did is in the results. But no data for SalesOrderId will show up in any query on the table, temporal or otherwise:<\/p>\n SELECT * Returns nothing.<\/p>\n This is all quite interesting, and I hope that it helps you as a reader someday when you are wanting to try out some of these scenarios. In this blog I wanted to look as some esoteric situations for how temporal would work, leading up to how you might need to think of things when you are modifying logical objects of data, rather than just one independent row at a time.<\/p>\n","protected":false},"excerpt":{"rendered":" Back last June, I started this series on temporal tables, and in my head, I had enough information to make an educated set of examples on how to use them. In the back of my mind though, I knew that I hadn’t quite thought enough about the whole process, particularly when you have several rows……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82196","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82196","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82196"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82196\/revisions"}],"predecessor-version":[{"id":85643,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82196\/revisions\/85643"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82196"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nINSERT INTO\u00a0 Sales.SalesOrder (SalesOrderID, Data)
\nVALUES (1,’Original’);<\/p>\n
\n\u00a0
\nSELECT *
\nFROM\u00a0\u00a0\u00a0 Sales.SalesOrder;
\nSELECT *
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory;<\/p>\n
\n———— ————————- ————————— —————————
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999<\/p>\n
\n———— ————————- ————————— —————————<\/p>\n
\n–WAITFOR DELAY ’00:00:01′; — Use a WAITFOR if you want to test this stuff in a single batch, or the times might always be the same.<\/p>\n
\nSET\u00a0\u00a0\u00a0 Data = ‘First Change’
\nWHERE\u00a0 SalesOrderID = 1<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder;
\nSELECT *
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory;<\/p>\n
\n\u00a0<\/p>\n
\n———— ————————- ————————— —————————
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 First Change\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999<\/p>\n
\n———— ————————- ————————— —————————
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225<\/p>\n
\n\u00a0
\nUPDATE Sales.SalesOrder
\nSET\u00a0\u00a0\u00a0 Data = ‘Second Change’
\nWHERE\u00a0 SalesOrderID = 1;<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder;
\nSELECT *
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory;<\/p>\n
\n———— ————————- ————————— —————————
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Second Change\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999<\/p>\n
\n———— ————————- ————————— —————————
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 First Change\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder FOR SYSTEM_TIME\u00a0 AS OF ‘2016-04-30 22:08:48.1200225’;<\/p>\n
\n———— ————————- ————————— —————————
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Second Change\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\nYou just see the data. This is as we expected, knowing something of how this stuff works from Part 1. Yeah, I had to look it up too). AS OF uses:<\/p>\n\n
\nVALUES (2,’Original’);<\/p>\n
\nVALUES (3,’Original’);<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder
\nWHERE\u00a0 SalesOrderId IN (2, 3);
\nSELECT *
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory
\nWHERE\u00a0 SalesOrderId IN (2, 3);<\/p>\n
\n———— —————————— ————————— —————————
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:13:19.1927715 9999-12-31 23:59:59.9999999
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:13:20.2358806 9999-12-31 23:59:59.9999999<\/p>\n
\n———— —————————— ————————— —————————<\/p>\n
\nSET\u00a0\u00a0\u00a0 Data = SYSDATETIME()
\nWHERE\u00a0 SalesOrderId = 2;<\/p>\n
\nSET\u00a0\u00a0\u00a0 Data = SYSDATETIME()
\nWHERE\u00a0 SalesOrderId = 3;<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder
\nWHERE\u00a0 SalesOrderId IN (2, 3);
\nSELECT *
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory
\nWHERE\u00a0 SalesOrderId IN (2, 3);<\/p>\n
\n———— —————————— ————————— —————————
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 17:17:16.0944986\u00a0\u00a0\u00a0 2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 17:17:18.0999866\u00a0\u00a0\u00a0 2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999<\/p>\n
\n———— —————————— ————————— —————————
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:13:19.1927715 2016-04-30 22:17:16.0944986
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:13:20.2358806 2016-04-30 22:17:16.0944986<\/p>\n
\nVALUES (4, SYSDATETIME());<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder
\nWHERE\u00a0 SalesOrderId IN (2, 3, 4);
\nSELECT *
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory
\nWHERE\u00a0 SalesOrderId IN (2, 3, 4);<\/p>\n
\n———— —————————— ————————— —————————
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 17:17:16.0944986\u00a0\u00a0\u00a0 2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 17:17:18.0999866\u00a0\u00a0\u00a0 2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 17:18:17.5493927\u00a0\u00a0\u00a0 2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999<\/p>\n
\n———— —————————— ————————— —————————
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:13:19.1927715 2016-04-30 22:17:16.0944986
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:13:20.2358806 2016-04-30 22:17:16.0944986<\/p>\n
\nVALUES (5, ‘Original’);<\/p>\n
\nSET\u00a0\u00a0\u00a0 Data = ‘First Change’
\nWHERE\u00a0 SalesOrderID = 5<\/p>\n
\nSET\u00a0\u00a0\u00a0 Data = ‘Second Change’
\nWHERE\u00a0 SalesOrderID = 5;<\/p>\n
\nWHERE SalesOrderId = 5;<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder
\nWHERE\u00a0 SalesOrderId = 5;<\/p>\n
\n———— —————————— ————————— —————————<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrderHistory
\nWHERE\u00a0 SalesOrderId = 5;<\/p>\n
\n———— —————————— ————————— —————————
\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619
\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 First Change\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619
\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Second Change\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619<\/p>\n
\nFROM\u00a0\u00a0 Sales.SalesOrder FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)
\nWHERE\u00a0 SalesOrderId = 5;<\/p>\n