Reasons to upgrade to SQL 2008

With the release of SQL Server 2008 RTM and the associated VS 2008 SP1 (now available indpendantly), developers can get their hands on the host of new features available.  SQL IntelliSense has been a long time coming; geo-location features are well timed given the proliferation of GPS in mobile devices; and full-text searching has been beefed up and fully integrated into the database engine.

One of my favourite albeit less hyped new features is the Change Data Capture (CDC) support.  A screen castfrom the beta available last December illustrates how this technology captures data changes made to tables; and it does so directly from the transaction logs without the need for triggers.

Enabling CDC to will create individual SQL jobs, tables and functions to store and fetch the information that has changed.  All inserts and deletes are individually record, and updates have both a before and after row.  CDC allows you to retrieve the aggregate changes to a table within a particular time period with some specifically named functions under its own ‘cdc’ schema.

Going hand-in-hand with CDC is the integrated MERGE T-SQL statement which allows wholesale update/insert/delete operations on a target table that matches particular source criteria.