CODEWITHSUNDEEP
sql-serversql-server-2012
mindbdev
mindbdev

Reputation: 444

Tracking SQL Server Table data changes

We are currently working in a Healthcare application. Since medical data are very sensitive , we may have to keep history of all changes made to some medical records. I know that we can do this by keeping history tables for logging in the changes. But keeping history tables will cause the database to grow large. So I wonder if there is any features in SQL Server to track the complete data changes of a table. I have heard about Change Data Capture feature.Is it applicable in my scenario ?? Thanks in advance

Upvotes: 0

Views: 695

Answers (1)

Tim
Tim

Reputation: 2922

It depends greatly on what you need to track. First of all, anything that tracks all history will use lots more space, and assuming the same level of detail, will not differ significantly in his much space it needs. Data is data, and it just takes space to store. However, with severally of your options you could look at storing your audit info outside of SQL if you wanted, and that helped.

Second, you have at least three immediately obvious options.

  1. Use SQL Triggers on tables to create history tables. This does allow you to include as many, or as few columns as you want, so that is one nice feature.

  2. Use SQL Change Tracking to monitor for changes and create history tables or otherwise audit the changes. Change tracking just stores that there were changes, it is up to you to store what the change was. Since this is queryable you can store it relatively easily outside the dB if desired.

  3. Use SQL Change Data Capture as mentioned. This is really heavyweight, and has performance and storage implications. However, it will do most of the work for you, and is relatively easy to use.

Upvotes: 2

Related Questions