locked
Change Data Sets RRS feed

  • Question

  • Hi,

    What is your recommendation for getting change data sets? I have used SQL CDC and have played a wee bit with temporal tables.

    I needed some help from folks who have used temporal tables :

    a) How does one deal with temporal tables when they become large in size? Is there like an archival strategy. If there is then what happens if an old data row changes - how would that be tracked( assuming the old rows have been archived?)

    b) Given it is datetime based is there a possibility to miss rows? based on the precision ( last few seconds etc). Probably not but just confirming.

    Any other things I need to be aware of while using temporal tables for Changed Data Sets.

    Would you recommend it over SQL CDC? If yes, Why?

    Thanks in anticipation,

    N

    Thursday, August 24, 2017 11:23 AM

Answers

  • Hi Nimish Rao,

     

    >>How does one deal with temporal tables when they become large in size? Is there like an archival strategy. If there is then what happens if an old data row changes - how would that be tracked( assuming the old rows have been archived?)

     

    Yes, we can look it as an archival strategy, each records in history will record in the history table. It will record the start-time and end-time together with the old data record in the history table. Please refer to the following example:

     

    I have initial three records in table -> (1,2,3),(2,4,5),(3,0,1). Then I delete (2,4,5) and change the value of A where ID =3 from 5-10.

     


    >>Given it is datetime based is there a possibility to miss rows? based on the precision ( last few seconds etc). Probably not but just confirming.

     

    You can refer to this document to know how does it work: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables#how-does-temporal-work, based on my test, it will not miss rows and if one record has not been submitted, it will not record.

     

    >>Would you recommend it over SQL CDC? If yes, Why?

     

    This depends on your requirement, actually, CDC and temporal tables are two different features. Temporal tables has many limitations, please refer to this: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations

     

    Temporal table is designed to solve historical data problems while CDC not. Besides, based on my test, temporal table is faster than CDC. I suggest you referring to this case: https://stackoverflow.com/questions/800331/why-do-we-need-a-temporal-database

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Friday, August 25, 2017 7:07 AM