SQL 2008 - Change Tracking made simple.

Hello all, this is my first blog entry. I guess I am officially a development nerd now. I will add things I deem cool to this page, relevant to programming and my "working holiday" in South Australia...

now for the good stuff!!

SQL 2008 is now available to developers, as well as the Visual Studio 2008 Service Pack 1. I installed the Visual Studio service pack and SQL 2008 Enterprise evaluation today. I was surprised how powerful SQL 2008 has become. I was also surprised that it took me half a day to install this stuff, but only 10 minutes to whip up a working example of "Change Tracking", a SQL 2008 feature that is of much interest to the project I am currently developing on.

Here is a quick example on how to test Change Tracking, to see if its worthwhile for you. I dug around on the internet, and quite frankly, I am not too impressed with the documented code examples for SQL 2008. They are few and far between.

Hopefully this can help a few "blokes" enable some seriously powerful enhancements to their databases. Or "sludgebeasts" as we call them here.


Here is a script to view change tracking on a Northwind database:
--Step 1:
--Setup Northwind database for Change Tracking
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE Northwind
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

ALTER TABLE Northwind.dbo.Products
ENABLE CHANGE_TRACKING

--Step 2:
--Modify some data. I did it manually, in the query editor.
--Ideally we would want update/insert/delete DML statements here

--Step 3:
--Query the changes (changes were done manually)
select * from CHANGETABLE (Changes Northwind.dbo.products, 1) as Test1

--Step 4:
--View the modified records
--using the ID's produce from the previous query
select * from Northwind.dbo.products
where Northwind.dbo.products.ProductID IN (2,3,4,5)



Here is a link to download the Northwind/Pubs database script from Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Simply install the script package. Once installed, open up SQL 2008 and do a "File...Open...File..." and point it to the installation directory you used for the script package. It will see the scripts, so load them, run them and enjoy!!