SQL Server 2008 Change Data Capture in 60 seconds

SQL Server 2008 Change Data Capture (CDC) is a management feature that allows you to track changes to the structure and content of tables in a database. Changes as a result of DML and DDL statements are registered asynchronously, so CDC is a welcome alternative to synchronous tracking features like table, database, and server triggers.

Let's start with the creation of a demo database with a table:

/********************************************/

/*                                          */

/* SQL Server 2008 Change Data Capture Demo */

/*                                          */

/********************************************/

/***********************************************************/

/* Preparation: Create a database and a table to play with */

/***********************************************************/

USE [master]

GO

IF EXISTS (SELECT name FROM sys.databases 

           WHERE name = N'DockOfTheBay')

DROP DATABASE [DockOfTheBay]

GO

CREATE DATABASE DockOfTheBay 

GO

USE [DockOfTheBay]

GO

CREATE TABLE [dbo].[TrackedTable]

(

    [ID] [int] NOT NULL,

    [Name] [varchar](100) NULL,

    CONSTRAINT [PK_TrackedTable] PRIMARY KEY CLUSTERED 

    (

        [ID] ASC

    )

)

GO

Before you can use it on a table, CDC needs to be enabled at database level:

/**********************************/

/* Enabling CDC at database level */

/**********************************/

/* Check if CDC is enabled at DB level */

SELECT is_cdc_enabled 

  FROM sys.databases 

 WHERE [name] = 'DockOfTheBay'

/* Activate CDC at DB level */

EXEC sys.sp_cdc_enable_db

GO

As a result of this activation, a schema with the name of cdc is generated and populated with some system tables:



When CDC is enabled for the database, you can enable it for each table that you want to track. Here's how this goes:

/*******************************/

/* Enabling CDC at table level */

/*******************************/

/* Activate CDC at table level */

EXEC sys.sp_cdc_enable_table 

     @source_schema = 'dbo', 

       @source_name = 'TrackedTable',

         @role_name = 'CDC_Admin'

GO

/* Check if CDC is enabled at table level */

SELECT is_tracked_by_cdc 

  FROM sys.tables 

 WHERE [name] = 'TrackedTable'

The system tables in the cdc schema get populated with metadata about the tracking: they will hold tables and columns that are being tracked:

/* Check tracking meta data */

SELECT *

  FROM cdc.change_tables

SELECT *

  FROM cdc.captured_columns

GO

Using CDC requires the SQL Server Agent to be running, since he will run two jobs that control the log readers:



The engine is running now, so let's give CDC a test drive. I'm first going to add a column to the tracked table. Shortly after that -remember, it's not synchronous- the ddl_history table in the cdc schema should reflect the change:

/***************/

/* Tracing DML */

/***************/

/* Modify Table Structure */

ALTER TABLE dbo.TrackedTable

ADD [Description] VARCHAR(255)

GO

/* Check (Please give the SQL Agent Job some slack) */

SELECT * 

  FROM cdc.ddl_history

GO

In SQL Server 2005, you can get the same result with event notifications, but that's a little bit more cumbersome (note: this is an understatement).

Let's see what happens if we apply some DML statements against the table. For each tracked table, a mirror table is created that holds the changes:

/***************/

/* Tracing DML */

/***************/

/* Modify Table Contents */

INSERT INTO [DockOfTheBay].[dbo].[TrackedTable]

     VALUES (1, 'X', 'X'),(2, 'Y', 'Y')

/* Check Table */

SELECT *

  FROM cdc.dbo_TrackedTable_CT

/* More DML */

UPDATE dbo.TrackedTable

   SET [Name] = 'Z'

 WHERE ID = 1

 DELETE dbo.TrackedTable

  WHERE ID = 2

 UPDATE dbo.TrackedTable

    SET [Description] = 'Ignored :-('

 GO

/* Check Table */

SELECT *

  FROM cdc.dbo_TrackedTable_CT

As you can see, the Description column that was added to the table, is not tracked. I'll solve that in a minute.

The __$operation column identifies the DML operation (insert, before update, after update, delete, or merge):

/* Check Table 2 */

SELECT CASE __$operation 

            WHEN 1 THEN 'Delete'

            WHEN 2 THEN 'Insert'

            WHEN 3 THEN 'Before Update'

            WHEN 4 THEN 'After Update'

            WHEN 5 THEN 'Merge' 

       END AS Operation,

       ID,

       [Name]

  FROM cdc.dbo_TrackedTable_CT

Let's walk the whole mile and reverse engineer the contents of the tracking table back to DML:

/* Regenerate DML */

SELECT CASE __$operation 

            WHEN 1 THEN 

                'DELETE FROM TrackedTable' + 

                ' WHERE id = ' + CONVERT(VARCHAR(20), id) +

                ' AND [Name] = ''' + [Name] + ''''

            WHEN 2 THEN 

                'INSERT INTO TrackedTable' +

                '(id, [Name]) VALUES (' +

                CONVERT(VARCHAR(20),id) + ', ''' +

                [Name] + '''' + ')'

            WHEN 3 THEN 

                'DELETE FROM TrackedTable'+ 

                ' WHERE id = ' + CONVERT(VARCHAR(20), id) +

                ' AND [Name] = ''' + [Name] + ''''

            WHEN 4 THEN 

                'INSERT INTO TrackedTable' +

                '(id, [Name]) VALUES (' +

                CONVERT(VARCHAR(20),ID) + ', ''' +

                [Name] + '''' + ')'

            WHEN 5 THEN 

                'MERGE ...' 

       END AS [Regenerated DML]

  FROM cdc.dbo_TrackedTable_CT

 ORDER BY __$start_lsn

Nice, isn't it? Well, unfortunately some of the changes were not registered, since CDC only tracks the columns that existed when the tracking was initiated for the table. It is unaware of my Description column, so let's bring that column in the scope:

/********************************/

/* Changing tracking properties */

/********************************/

/* Disable CDC at table level             */

/* WARNING: this drops the tracking table */

EXEC sys.sp_cdc_disable_table

        @source_schema = 'dbo', 

          @source_name = 'TrackedTable',

     @capture_instance = 'dbo_TrackedTable'

GO

 /* Table is gone ... */

SELECT *

  FROM cdc.dbo_TrackedTable_CT

/* Re-enable with a specific column list */

EXEC sys.sp_cdc_enable_table 

            @source_schema = 'dbo', 

              @source_name = 'TrackedTable',

                @role_name = 'CDC_Admin',

     @captured_column_list = 'ID, Name, Description' 

GO

/* Check columns */

SELECT *

  FROM cdc.captured_columns

/* Table is back */

SELECT *

  FROM cdc.dbo_TrackedTable_CT

And finally, the cleanup:

/***********/

/* Cleanup */

/***********/

/* Disable CDC */

EXEC sys.sp_cdc_disable_db

GO

/* SQL Agent Jobs are gone ... */

SELECT *

  FROM msdb.dbo.sysjobs

 WHERE [name] LIKE 'cdc.%'

USE [master]

GO

/* Drop db */

DROP DATABASE DockOfTheBay

GO

SQL Server 2008 Sparse Columns in 60 seconds

SQL Server's most recent releases contain features that allow us to improve the response time of queries against very large tables (large in the sense of 'many rows'):

• Partitioning (introduced in SQL Server 2005) allows us to spread tables and/or indexes over multiple filegroups, and
• Filtered Indexes (introduced in SQL Server 2008) allow us to place indexes on parts of a table.

On top of that, SQL Server 2008 introduces a feature that efficiently deals with large tables, this time in the sense of 'many columns': the so-called Sparse Columns.

A lot of data models in the real world have tables that have many many columns containing just a NULL value. Unfortunately a regular NULL value consumes some physical space, so large quantities of those can cause performance decrease. SQL Server 2008's sparse columns are columns with optimized storage of NULL values: NULLs just take no space at all. Of course there's a down side: a small overhead (2 or 4 extra bytes) for non-NULL values. So a sparse column is meant to be used when a column has lots of NULL values (sparsely filled).

Here's a small example. I create two tables, a regular one and one containing sparse columns, I populate them, and then compare sizes:

/***************/

/* Preparation */

/***************/

USE AdventureWorks2008

GO

CREATE SCHEMA DockOfTheBay

GO

/**************************************/

/* Step One: Show Storage Improvement */

/**************************************/

-- Create two tables, one with 23 columns, and one with 23 columns but 20 sparse.

CREATE TABLE DockOfTheBay.TableWithoutSparseColumns (

   ProductKey INT IDENTITY, ProductName VARCHAR (100), CategoryID INT,

   col01 INT NULL, col02 INT NULL, col03 INT NULL, 

   col04 INT NULL, col05 INT NULL, col06 INT NULL,

   col07 INT NULL, col08 INT NULL, col09 INT NULL, 

   col10 INT NULL, col11 INT NULL, col12 INT NULL,

   col13 INT NULL, col14 INT NULL, col15 INT NULL, 

   col16 INT NULL, col17 INT NULL, col18 INT NULL,

   c1019 INT NULL, col20 INT NULL);

GO

CREATE TABLE DockOfTheBay.TableWithSparseColumns (

   ProductKey INT IDENTITY, ProductName VARCHAR (100), CategoryID INT,

   col01 INT SPARSE NULL, col02 INT SPARSE NULL, col03 INT SPARSE NULL, 

   col04 INT SPARSE NULL, col05 INT SPARSE NULL, col06 INT SPARSE NULL,

   col07 INT SPARSE NULL, col08 INT SPARSE NULL, col09 INT SPARSE NULL, 

   col10 INT SPARSE NULL, col11 INT SPARSE NULL, col12 INT SPARSE NULL,

   col13 INT SPARSE NULL, col14 INT SPARSE NULL, col15 INT SPARSE NULL, 

   col16 INT SPARSE NULL, col17 INT SPARSE NULL, col18 INT SPARSE NULL,

   c1019 INT SPARSE NULL, col20 INT SPARSE NULL);

GO

-- Populate the tables

DECLARE @Counter int

SET @Counter = 0

WHILE @Counter < 10000

BEGIN

    INSERT INTO DockOfTheBay.TableWithSparseColumns 

                (ProductName, CategoryID) VALUES ('aaaa', 1);

    INSERT INTO DockOfTheBay.TableWithSparseColumns 

                (ProductName, CategoryID, col01) VALUES ('bbbb', 2, 46);

    INSERT INTO DockOfTheBay.TableWithSparseColumns 

                (ProductName, CategoryID, col02) VALUES ('cccc', 3, 44);

    INSERT INTO DockOfTheBay.TableWithSparseColumns 

                (ProductName, CategoryID, col01, col02) VALUES ('dddd', 4, 12, 34);

    INSERT INTO DockOfTheBay.TableWithSparseColumns 

                (ProductName, CategoryID, col12, col13, col14, col15) VALUES ('eeee', 4, 12, 34, 46, 66);

    INSERT INTO DockOfTheBay.TableWithoutSparseColumns 

                (ProductName, CategoryID) VALUES ('aaaa', 1);

    INSERT INTO DockOfTheBay.TableWithoutSparseColumns 

                (ProductName, CategoryID, col01) VALUES ('bbbb', 2, 46);

    INSERT INTO DockOfTheBay.TableWithoutSparseColumns 

                (ProductName, CategoryID, col02) VALUES ('cccc', 3, 44);

    INSERT INTO DockOfTheBay.TableWithoutSparseColumns 

                (ProductName, CategoryID, col01, col02) VALUES ('dddd', 4, 12, 34);

    INSERT INTO DockOfTheBay.TableWithoutSparseColumns 

                (ProductName, CategoryID, col12, col13, col14, col15) VALUES ('eeee', 4, 12, 34, 46, 66);

    SET @Counter = @Counter + 1

END

GO

-- Check average row size, and number of pages

SELECT [avg_record_size_in_bytes], [page_count] FROM sys.dm_db_index_physical_stats (

   DB_ID ('AdventureWorks2008'), 

   OBJECT_ID ('DockOfTheBay.TableWithoutSparseColumns'), 

   NULL, NULL, 

   'DETAILED');

SELECT [avg_record_size_in_bytes], [page_count] FROM sys.dm_db_index_physical_stats (

   DB_ID ('AdventureWorks2008'), 

   OBJECT_ID ('DockOfTheBay.TableWithSparseColumns'), 

   NULL, NULL, 

   'DETAILED');

GO

Observe that the classic table consumes twice the space of the one with sparse columns:



Sparse columns have also another advantage. Internally all sparse columns are grouped into one column. That implies that we can pass the treshold of 1024 columns per table: a table can have up to 100.000 sparse columns! You can make this internal column accessible via a Column Set. But be careful, this has an effect on the SELECT * behavior:

/************************************/

/* Step Two: Demonstrate Column Set */

/************************************/

-- Create Table with Column Set

CREATE TABLE DockOfTheBay.TableWithSparseColumnsAndColumnSet (

   ProductKey INT IDENTITY, ProductName VARCHAR (100), CategoryID INT,

   col01 INT SPARSE NULL, col02 INT SPARSE NULL, col03 INT SPARSE NULL, 

   col04 INT SPARSE NULL, col05 INT SPARSE NULL, col06 INT SPARSE NULL,

   col07 INT SPARSE NULL, col08 INT SPARSE NULL, col09 INT SPARSE NULL, 

   col10 INT SPARSE NULL, col11 INT SPARSE NULL, col12 INT SPARSE NULL,

   col13 INT SPARSE NULL, col14 INT SPARSE NULL, col15 INT SPARSE NULL, 

   col16 INT SPARSE NULL, col17 INT SPARSE NULL, col18 INT SPARSE NULL,

   c1019 INT SPARSE NULL, col20 INT SPARSE NULL,

   SparseColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

GO

-- Populate the Table

DECLARE @Counter int

SET @Counter = 0

WHILE @Counter < 10000

BEGIN

    INSERT INTO DockOfTheBay.TableWithSparseColumnsAndColumnSet 

                (ProductName, CategoryID) VALUES ('aaaa', 1);

    INSERT INTO DockOfTheBay.TableWithSparseColumnsAndColumnSet 

                (ProductName, CategoryID, col01) VALUES ('bbbb', 2, 46);

    INSERT INTO DockOfTheBay.TableWithSparseColumnsAndColumnSet 

                (ProductName, CategoryID, col02) VALUES ('cccc', 3, 44);

    INSERT INTO DockOfTheBay.TableWithSparseColumnsAndColumnSet 

                (ProductName, CategoryID, col01, col02) VALUES ('dddd', 4, 12, 34);

    INSERT INTO DockOfTheBay.TableWithSparseColumnsAndColumnSet 

                (ProductName, CategoryID, col12, col13, col14, col15) VALUES ('eeee', 4, 12, 34, 46, 66);

    SET @Counter = @Counter + 1

END

-- Compare SELECT *

SELECT * FROM DockOfTheBay.TableWithoutSparseColumns WHERE ProductKey < 11

SELECT * FROM DockOfTheBay.TableWithSparseColumnsAndColumnSet WHERE ProductKey < 11

GO

/***********/

/* Cleanup */

/***********/

DROP TABLE DockOfTheBay.TableWithoutSparseColumns

DROP TABLE DockOfTheBay.TableWithSparseColumns

DROP TABLE DockOfTheBay.TableWithSparseColumnsAndColumnSet

DROP SCHEMA DockOfTheBay