I wanted to see how this trigger solution compares to an actual temporal table. One reason many people loath triggers is due to their potential for bad performance (particular when many triggers get chained together). BirdsHistory ) FakeTemporal WHERE SYSTEM_TIME >= SysStartTime AND SYSTEM_TIME < SysEndTime Real Performance Now seeing what our dbo.Birds data looked like at a certain point-in-time isn't quite as easy as a system versioned table in SQL Server 2016, but it's not bad:ĭECLARE SYSTEM_TIME datetime2 = ' 16:30:11' SELECT * FROM ( SELECT * FROM dbo. If you run each of those batches one at a time and check both tables, you'll see how the dbo.BirdsHistory table keeps track of all of our data changes. Birds SET SightingCount = SightingCount + 1 WHERE id = 4 GO ROLLBACK
Dbo chroma key live youtube update#
Birds WHERE id in ( 2, 3 ) GO BEGIN TRANSACTION UPDATE dbo. Birds SET SightingCount = SightingCount + 1 WHERE id = 4 GO ROLLBACK /* deletes */ DELETE FROM dbo. Birds SET SightingCount = SightingCount + 1 WHERE id in ( 2, 3 ) GO BEGIN TRANSACTION UPDATE dbo. Birds SET SightingCount = SightingCount + 1 WHERE id = 1 GO UPDATE dbo. Birds ( BirdName, SightingCount ) VALUES ( 'Ivory Billed Woodpecker', 1 ) ROLLBACK GO /* updates */ UPDATE dbo. Birds ( BirdName, SightingCount ) VALUES ( 'Dodo', 1 ) INSERT INTO dbo. Birds ( BirdName, SightingCount ) VALUES ( 'Nuthatch', 1 ) COMMIT GO BEGIN TRANSACTION INSERT INTO dbo. Birds ( BirdName, SightingCount ) VALUES ( 'Canada Goose', 1 ) INSERT INTO dbo. Birds ( BirdName, SightingCount ) VALUES ( 'Cardinal', 1 ) GO BEGIN TRANSACTION INSERT INTO dbo. Birds ( BirdName, SightingCount ) VALUES ( 'Blue Jay', 1 ) GO INSERT INTO dbo.
I don't want to have to manage my own operational versus historical data and write complicated queries for "point-in-time" analysis, so I decided to fake temporal table functionality using triggers.Ĭreating the base table and history table are pretty similar to that of a temporal table, just without all of the fancy PERIOD and GENERATED ALWAYS syntax: They are easy to use and I like having SQL Server manage my data for me automatically. Now, this isn't a huge issue I can still write queries on those servers to achieve the same result as I would get with temporal tables.īut temporal tables have made me spoiled. Sometimes I can't use temporal tables though, like when I'm forced to work on an older version of SQL Server. When temporal tables were added in SQL Server 2016 I was quick to embrace them.Ī lot of the data problems I work on benefit from being able to view what data looked like at a certain point back in time, so the easy setup and queriability of temporal tables was something that I immediately loved.
The one exception is when I need a poor man's temporal table. I don't shy away from them because of some horrible experience I've had, but rather I rarely have a good need for using them. Triggers are something that I rarely use.
0 kommentar(er)
