SQL Server Change Tracking - SYS_CHANGE_VERSION and Replication

• angeloc 83733

  SSC Veteran

  Points: 210

  More actions

  December 7, 2015 at 2:57 pm

  #323161

  My goal is to use SQL Server change tracking to track changes to be pushed to another non-SQL system.

  My database is a subscriber. Numerous publishers are pushing transactions into numerous tables.

  During my testing, I observed that the entries recorded by change tracking has records grouped into 'chunks'. I.E. There are many records with the same value of SYS_CHANGE_VERSION.

  This is based on my query of select * from changetable(CHANGES my_table, 0)

  Observation: When I execute the above query, some of the rows are 'interleaved'.

  For example, I may have 50 row with SYS_CHANGE_VERSION = 100, 1 row with SYS_CHANGE_VERSION = 101 and then more rows with SYS_CHANGE_VERSION = 100.

  My question:

  When rows are inserted into the change tracking table/mechanism, can rows be inserted with a lower value of SYS_CHANGE_VERSION than the current maximum value?

  I am trying to determine if all I need to do is have a sort order on SYS_CHANGE_VERSION and simple ignore records for the current max value of SYS_CHANGE_VERSION.

  As an alternative, I have considered using the replication stored procedures on my subscriber to push changes to my non-SQL system.

  Any answers appreciated.