How SQL Server inserts record into CDC table (with Latency)

• Tarun Jaggi

  Hall of Fame

  Points: 3365

  More actions

  May 2, 2013 at 4:00 am

  #273539

  Hi All,

  I have a question related to CDC latency in SQL Server 2008 R2.

  Suppose I have two tables T1 and T2 with the following schema:

  T1:

  ID(int), Name(varchar), InsertionDate(datetime), Rowversion(timestamp)

  T2:

  ID(int), Name(varchar), T1ID(int), InsertionDate(datetime), Rowversion(timestamp)

  CDC is enabled on both the above tables. Now I have inserted one record in each table (with assumption that 5 mins latency in CDC is there):

  T1:

  ID, Name, InsertionDate, Rowversion

  1, 'test1', '2013-05-02 01:00:00', 0x0001

  T2:

  ID, Name, T1ID, InsertionDate, Rowversion

  100, 'testt1', 1, '2013-05-02 01:00:30', 0x0020

  (Note: In table T1 the record is first and after 30 secs the record is inserted into T2)

  As we know that CDC picks the data from transaction logs, so my question is it possible that CDC commits the data into T2_CT first and then T1_CT??