Change Tracking in SQL Server
SQL Server 2008 introduced new features for auditing data changes. They are both available on a per-database basis and yet both have distinctly different purposes.
Change Data Capture monitors the Transaction log and records changes to data. The changes are stored in change tables managed by SQL Server Service. This provides a history of changes over time. The data is available for querying.
In contrast, Change Tracking is used to maintain a row version indicator for each row in tables. To enable this capability, SQL Server creates a tracking table with the primary key and a row version column. Each time data is modified the row version is modified. Then, when users wish to modify a row using optimistic concurrency, they can be notified if the row was modified by another user, and their data may be obsolete.
This Change Tracking technique is similar to adding a Row Version column in tables you wish to use this feature against. The main difference is that you can turn on Change Tracking without modifying your table schema. SQL Server creates new tables for tracking row changes and manages the one to one relationship between your data table and the change table.
The change tracking technique does not store any history. It simply synchronizes and simplifies optimistic concurrent transaction resolution. Without this sort of mechanism, SQL would have to compare the before image of every column in a table prior to performing an update with the current value in a database in order to assure another user had not modified the data since you retrieved it from the database. That’s a lot of overhead.
Reader Response on Hypothetical Indexes
Jeremy Writes:
I think you might be missing some features of DETA (or I am completely misunderstanding your point). DETA will let you focus on particular tables within your database rather than just the whole thing, and the input file can be just a single query as well (saved as a .sql file). If I get to the point where I am tearing my hair out with a poorly-performing query, then I will often do just this to see where I am going wrong.
The Change Tracking SQL Server feature is a great optimization technique. This is especially true if you are using ORM engines such as NHibernate or Entity Framework. Do you have other tools or techniques you use to optimize your ORM engines when working with SQL Server? How about MySQL, or other data stores? How about you Oracle gurus; what options are available to you there? Get into the conversation by writing btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
SSWUGtv
With Stephen Wynkoop
How do you maintain security through encryption, WITHOUT killing your server? Patrick Townsend is on today’s show with some priceless tips.
Watch the Show
Featured Article(s)
Working with Parallel LINQ
This article reflects on parallel programming concepts, PLINQ, discusses the new features and enhancement to the Parallel Extensions Library, and reflects on how to implement declarative data parallelism in your applications using PLINQ. It also discusses the tips and techniques to better leverage the benefits of PLINQ.
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)
Featured Script
dba3_fn_Set_SuperSets_Article
Used in demonstrating a Maps and Sets logical design of a hierarchical system. Use with: Modeling Hierarchical Relationships … (read more)