Search Results for change tracking

Change Tracking Inserts Require SELECT Permission

I’ve been writing about change tracking in SQL Server for some time now, but I recently came across a permissions issue on a change tracking table that I’d never encountered before. When attempting to insert data into a change tracking-enabled table using an account with no select permissions, I learned that the effective security principal must have both insert and…


Video: Introduction to SQL Server Change Tracking

SQL Server change tracking is a lightweight and synchronous mechanism for detecting inserts, updates, and deletes in tracked tables. For those of us who spend a lot of time in ETL, change tracking is a useful tool for defining what data has changed to make the extract-transform-load process more efficient. SQL Server Change Tracking In this 30-minute video, I introduce…


Using Change Tracking in SSIS

Recently, I wrote about how to get started with SQL Server change tracking, and I demonstrated a design pattern I use with change tracking in incremental load scenarios. In this post, I’ll round out the topic by showing how using change tracking in SSIS packages can add more flexibility to ETL processes. Using Change Tracking in SSIS In my last post I…


Using SQL Server Change Tracking for Incremental Loads

Earlier this week I wrote about the basics of change tracking in SQL Server, and showed how to get started using this technology for change detection. In this post, I’ll continue what I started by demonstrating how change tracking fits into a larger design pattern for end-to-end incremental load ETL processes. Incremental Load Overview ETL processes fall into one of…


Getting Started with Change Tracking in SQL Server

Change tracking for SQL Server is a flexible and easy-to-use technology for monitoring tables for inserts, updates, and deletes. In this post, I’ll discuss getting started with change tracking in SQL Server, and will show an example of how to get started with it. Change Tracking in SQL Server Change tracking is a lightweight mechanism for tracking which rows have…


Webinar: Change Detection in SQL Server

Change detection is a critical component of any system that moves data from one structure to another. Most data movement mechanisms are designed to move a subset of the data – only that which is new or changed since the last load. For that to work properly, there must be a reliable system for detecting delta data touched since the…


SQL Server 2016 Service Pack 1 Changes Everything

Earlier today, Microsoft announced the release of Service Pack 1 (SP1) for SQL Server 2016. Pay close attention to this announcement, because this is not a typical update. Service Pack 1 unlocks most all of the programmability features found in Enterprise Edition, making them available in lower-cost (and even free) editions of SQL Server. Folks, this is a huge change…


The What, Why, When, and How of Incremental Loads

When moving data in an extraction, transformation, and loading (ETL) process, the most efficient design pattern is to touch only the data you must, copying just the data that was newly added or modified since the last load was run. This pattern of incremental loads usually presents the least amount of risk, takes less time to run, and preserves the…


The Eleven Days of Festivus 2018

It’s that time again! My Eleven Days of Festivus blogging extravaganza is now in its third year, and I’m happy to share one blog post per day with you for the 11 days between now and Festivus Eve (December 22nd). As before, I’ll link all of the posts from this page. May your feats of strength be mighty, your airing…


Using Temporal Tables for Slowly Changing Dimensions

One of my favorite recent additions to SQL Server is the ability to use temporal tables to retain change history. As I wrote in an earlier post on this topic, temporal tables allow one to easily configure a table keep all of the changes (including updated and deleted rows) in a designated history table. In this post, I will share…