Obscure Problems and Gotchas

There is a very powerful feature in SQL Server that if you know how to take advantage of can cut down on the amount of logic you have to produce when performing deltas. This is very much a foundational implementation, not something you can necessarily introduce after the fact without going through a lot of pain. SQL Server provides a column type named rowversion which is a successor and replacement for the deprecated timestamp column type.

This methodology is great if you are importing a large amount of data every day and only want to promote what has actually changed since the last time the import occurred. This methodology can also be used to feed static reporting tables because only data that changed can be isolated.

Facts about rowversion

• You cannot insert or update the rowversion of a row, it is controlled by SQL server. Therefore upon insert or update the rowversion is changed automatically.
• Rowversion can be sorted and this is key for performing delta comparisons.
• A nonnullable rowversion column is semantically equivalent to a binary(8) column.
• A nullable rowversion column is semantically equivalent to a varbinary(8) column.
• Example of what rowversion looks like: 0x000000000005862F

Rules of engagement

The way to think about using rowversion for deltas is in two parts:

1. Add a rowversion column, which I typically name [Version] into a target table.
   1. The word “Version” is a reserved keyword which is why I use the square brackets.
2. Create a new table to track the max version of your target tables.

This will make more sense with the example that follows.

Example tables

The following tables are simplistic examples that each play a key role in the real world.

Example setup

The following example is designed to demonstrate how rowversion works with meaningless integer data. Here the tables are being created and they are being filled with random test data. My test output will be different from yours, but they will work the same if you are following along.

Full code example located on GitHub

dbo.DataThatChangesFrequently

Below you will see that a while loop is being used to fill the table. This is being done on purpose because the RAND() function will only return a different value in a query when it is called one time and a seed is NOT provided. Therefore it has to be looped. For demonstration purposes this is okay, but not ideal for real world of course.

CREATE TABLE dbo.DataThatChangesFrequently
(
	DataThatChangesFrequentlyId INT IDENTITY(1,1) NOT NULL,
	SomeNumber INT NOT NULL,
	[Version] ROWVERSION NOT NULL,
	CONSTRAINT [PK_dbo.DataThatChangesFrequently_DataThatChangesFrequentlyId] PRIMARY KEY(DataThatChangesFrequentlyId)
)

GO

DECLARE @i INT = 0;
DECLARE @count INT = 10;

WHILE @i < @count
BEGIN
	INSERT INTO dbo.DataThatChangesFrequently
	(
		SomeNumber
	)
	VALUES
	(RAND()*100000)
	
	SET @i += 1;
END

SELECT * FROM dbo.DataThatChangesFrequently

-- There is a method to the madness
SELECT MIN(Version), MAX(Version) FROM dbo.DataThatChangesFrequently

dbo.CollectionTracker

The setup for this table is always going to be an initial value because you need a baseline of where you are starting. If you are setting this up for the first time then using zero is safe because zero will be less than the rowversion provided by SQL Server.

CREATE TABLE dbo.CollectionTracker
(
	CollectionTrackerId INT IDENTITY(1,1) NOT NULL,
	[Key] VARCHAR(50) NOT NULL,
	[Description] VARCHAR(255) NULL,
	LastVersion CHAR(16) NOT NULL, -- Storing as char for the sake of portability
	CollectedOnUtc DATETIME2(0) NOT NULL,
	CONSTRAINT [PK_dbo.CollectionTracker_CollectionTrackerId] PRIMARY KEY (CollectionTrackerId)
)

-- Initializtion is a good first step to not bother with zero rows - zero rows will never matter after the first time so why bother now
-- Make this more sophisticated by adding in what it's tracking and don't track the same thing more than once
INSERT INTO dbo.CollectionTracker
(
    [Key]
   ,LastVersion
   ,CollectedOnUtc
)
VALUES
(   
    'DataThatChangesFrequently'
   ,'0000000000000000' -- Smallest version for now, don't store the 0x it will mess up the conversion
   ,GETUTCDATE() -- CollectedOnUtc - datetime2(0)
)

I have on many occasions tried storing the binary value as an actual binary data type, but for the life of me have had nothing by problems doing so. I feel like SQL Server has overcomplicated how binary is stored, but that’s a different problem for another time. Therefore to avoid making terrible mistakes with how the data is stored you can completely bypass using a binary data type and just substitute it for a CHAR(16).

dbo.ICollectNumbersForFun

As mentioned earlier this is a destination for the data in the source. This is where the power of the [Version] column and tracked last version come into play. You recall your “last max version” which by now with the passage of time and changes in the system is your new minimum version. Comparing the last version to your [Version] column as shown below will get you everything that has a greater version than your last (max) version. This literally exposes your delta.

CREATE TABLE dbo.ICollectNumbersForFun
(
	ICollectNumbersForFunId INT IDENTITY(1,1) NOT NULL,
	OooANumber INT NOT NULL,
	CONSTRAINT [PK_dbo.ICollectNumbersForFun_ICollectNumbersForFunId] PRIMARY KEY (ICollectNumbersForFunId)
)

GO

-- Ok Let's collect some numbers! First time synchronization usually means let's get everything
DECLARE @lastVersion binary(8);
DECLARE @maxVersion binary(8);

SELECT
	@lastVersion = CONVERT(binary(8), LastVersion, 2)
FROM dbo.CollectionTracker
WHERE [Key] = 'DataThatChangesFrequently'

SELECT
	@maxVersion = MAX([Version])
FROM dbo.DataThatChangesFrequently
WHERE [Version] > @lastVersion

SELECT 
	 @lastVersion AS LastVersion
	,@maxVersion AS MaxVersion

-- This is for demonstration purposes only - you would really want to do a manual upsert or use the merge construct
INSERT INTO dbo.ICollectNumbersForFun
(
    OooANumber
)
SELECT 
	SomeNumber 
FROM dbo.DataThatChangesFrequently
WHERE [Version] > @lastVersion

-- Mark down what the latest version was
UPDATE dbo.CollectionTracker SET
	CollectedOnUtc = GETUTCDATE(),
	LastVersion = CONVERT(CHAR(16), @maxVersion, 2)
WHERE [Key] = 'DataThatChangesFrequently'

SELECT * FROM dbo.CollectionTracker ct
SELECT * FROM dbo.ICollectNumbersForFun

This is just a demonstration example, but lets assume you do something meaningful with your delta data. After you perform your work it is important to capture your new “Last max version” and store it in the tracking table for the next time you process deltas.

Understanding how to process your deltas

There are three types of deltas and it is entirely up to the designer to implement the types of deltas needed as explained in the following table.

Beyond initial load

The above code only demonstrates initial load and inserts the deltas into an empty destination table. That’s not real world usually, but I think the idea is more clear now how to use the rowversion data type. Below is an example of coercing the rowversion to change in the source, however no processing code is provided.

SELECT * FROM dbo.DataThatChangesFrequently dtcf

DECLARE @i INT = 1;
DECLARE @count INT = 4;

WHILE @i <= @count
BEGIN
	UPDATE dbo.DataThatChangesFrequently SET
		SomeNumber = RAND()*100000
	WHERE DataThatChangesFrequentlyId = @i
		
	SET @i += 1;
END

SELECT * FROM dbo.CollectionTracker ct
SELECT * FROM dbo.DataThatChangesFrequently dtcf
SELECT * FROM dbo.ICollectNumbersForFun

GO

-- No way to reprocess unless there is a change
SELECT 
	* 
FROM dbo.DataThatChangesFrequently
WHERE [Version] > 0x000000000005862F -- Example of the last maximum value stored. Your value will be different. 

Benefits of this methodology

If you are performing imports of large datasets and the data is not already a delta, then this is very beneficial for not taxing your production systems with queries that perform redundant “Did this data change?” checks. Just by utilizing this method together with a staging table, you can figure out exactly what changed and only distribute those changes to your production environment. The first run of this methodology is usually the slowest because there is no baseline compare, but subsequent runs should be magnitudes in order faster because you will only be processing what changed.

What this methodology does not do

This methodology is great for telling you that a row of data has changed, but it will NOT tell you WHAT on the row changed. In order to know what changed on your row you can either:

• Perform a column by column comparison which is okay to do when you don’t have a lot of columns to compare.
• Use a Hashing algorithm to hash together all of your relevant columns and perform a single comparison on those hashes. There is only one drawback to the hashing approach and that is sometimes the hashing algorithm may not produce unique results, but it’s very unlikely.