SQL server change table query not working as expected

  • chatsubrato

    SSC Rookie

    Points: 32

    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: 80378

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

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply