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
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.
kekline @ twitter
P.S. Check out my new site - http://kevinekline.com