SQL server change table query not working as expected

• chatsubrato

  SSC Enthusiast

  Points: 172

  More actions

  February 14, 2020 at 1:33 pm

  #3725915

  Hi,

  It looks like that a CHANGETABLE query for a particular table is not working correctly.

  I created a record in the bill_details table and then updated it.

  The record was created with sys_change_version=5000.

  The records was updated with sys_change_version=5001.

  But the following CHANGETABLE queries are confusing me and apparently returning incorrect sys_change_creation_version:

  select * from CHANGETABLE(CHANGES bill_details, 5000) ct  order by bill_id;
  
  
  sys_change_version       sys_change_creation_version        sys_change_operation       sys_change_columns     sys_change_context     bill_id
  -------------------      -----------------------------      ----------------------     -------------------    --------------------   --------
  5001                     5001                                U                          NULL                   NULL                   2018
  
  
  select * from CHANGETABLE(CHANGES bill_details, 4999) ct  order by bill_id;
  
  
  sys_change_version       sys_change_creation_version        sys_change_operation       sys_change_columns     sys_change_context     bill_id
  -------------------      -----------------------------      ----------------------     -------------------    --------------------   --------
  5001                     5001                                I                          NULL                   NULL                   2018

  I would expect that if the sys_change_operation is 'U' then the sys_change_creation_version would be NULL.

  And if a record is updated and if I am searching using a smaller 'from version' number then the sys_change_version and sys_change_creation_versions would be different.

  Any ideas what I am missing? Or why these CHANGETABLE queries are behaving in an unexpected manner?

  Regards.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  February 15, 2020 at 2:10 pm

  #3726183

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• February 20, 2020 at 11:00 am

  This was removed by the editor as SPAM