I'm often asked to work on small projects for smaller companies. These are probably among my favorite because there is so much enthusiasm behind the team. I've been an architect, a DBA, a network engineer, a graphic designer, and sometimes all of the above at once. One thing I learned is small companies don't compromise on software architecture. It's easy to get caught up in the excitement when big decisions are made quickly and the whole team hits the ground running. As a DBA, I try to put good practices in place even when resource are limited and here is a result of one of my efforts. Change tracking for SQL Express 2008 using SQL Variant.

When companies can't afford SQL Enterprise licenses or Data Warehousing, it doesn't mean they don't need them. The fundamental principles of tracking data changes and deletes without restoring backups has always been a lofty target for DBAs to achieve. Unfortunately, this three-dimensional data modeling (Bi-Temporal Data Modeling) has been tricky to implement with what is essentially a two-dimensional database paradigm.

A table is a two-dimensional entity consisting of a (Row X Column) grid. Change tracking attempts to artificially inflate the two dimensional structure into three dimensions (Row X Column X Time). It's not easy, but it is quite useful. Bi-Temporal database theory has been around for decades. I'm not sure there is an established standard or practice to follow. I know that if your application is using a limited database such as SQL Server Express, there is no easy or practical way to handle change tracking without adding history tables to gobble up limited space and add complexity to the schema.

Well here's my solution. It's not perfect, but hopefully you will find it useful. The implementation is very simple and straightforward, and performance seems to be adequate. Watch the demo, try it out, let me know what you think.

Here are the files in the demo:

Demo Clip (10 minutes)



Intro Clip (5 minutes)