THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Bitemporal Data

Any IT pro with more than a year or two of experience will have faced the challenges of version control for an application, but what if you have to implement version control for data?

The most common way to tackle this problem is implementing something called "bitemporal data". Under this method, each row in a table includes the current valid time and the transaction time. Since two distinct time values are stored, we get the term bitemporal.

A great place to start is Adam Machanic's excellent article at http://www.simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/. In addition, I encourage you to check out Adam's book "Expert SQL Server 2005 Development" at http://www.apress.com/book/view/9781590597293.

In addition, Dejan Sarka pointed out that you can get a very comprehensive information on supporting temporal data from a book entitled "Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass. Mr. Snodgrass was so kind to publish the book in PDF format and make it available for free download at http://www.cs.arizona.edu/~rts/publications.html. (Note that the book is quite old now (year 1999), so the T-SQL code does not include any SQL Server 2005 or 2008 enhancements. But the basic SQL is still solid.

Cheers!

-Kevin

 kekline @ twitter

 P.S. Check out my new site - http://kevinekline.com

Published Tuesday, August 04, 2009 10:12 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jimmy May said:

Whether you're looking for info on bitemporal data, dynamic SQL, concurrency, error handling, or a handful of other topics, I also endorse Expert SQL Server 2005 Development (by not only Adam, but also Hugo Kornelis & Lara Rubbelke).

Their book is excellent--right up there at the top of the stack with Joe Sack's SQL Server 2008 Transact-SQL Recipes & Kalen et al's Internals--too bad the marketeers at Apress didn't do a better job.

August 4, 2009 10:26 AM
 

Mladen said:

I have to agreee with Jimmy May about the book thing. It really is great!

and if you haven't seen it yet tell Dejan to give his temporal SQL presentation. he presented it here in slovenia couple yeras back. pure awesome!

August 4, 2009 10:54 AM
 

Adam Machanic said:

Thanks for the kind words!

August 4, 2009 12:19 PM
 

Rajib Bahar said:

I read that well written article. It discussed about central truth, which made me think whether we're dealing with BI project or not. When he delved in to the details, I started to ponder whether he's talking about Slowly Changing Dimension 3 or SCD3. Then again, the solution did not add the extra column on the table as one would expect, but an extra ledger table. This implementation feels a lot like SCD 4 but for OLTP system and not OLAP. Is my summation on BiTemporal data is like implementing SCD4 on a OLTP system? Feel free to educate me on this. Thanks and I appreciated the reading about it.

November 4, 2009 1:01 PM
 

Lars Rönnbäck said:

I would like to recommend the only technique that fully embraces bitemporal data, Anchor Modeling. Look at http://www.anchormodeling.com for more information. There's even a "fix" for dimensional modeling that alleviates the need for SCD:s altogether.

June 24, 2012 1:02 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement