versioning – sqlsunday.com

Secure your temporal table history

You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.

CREATE SCHEMA App;

CREATE TABLE App.Customers (
    Company_ID      int IDENTITY(1, 1) NOT NULL,
    CompanyName     nvarchar(250) NOT NULL,
	Email           varchar(250) NOT NULL,
	Valid_From      datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	Valid_To        datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
	PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
) WITH (SYSTEM_VERSIONING=ON);

What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.

But how to you version the contents of a table, while hiding things like deleted records from prying eyes?

Copying data with foreign keys and/or identity columns

In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.

Somehow, that makes me a happier developer.

Efficient data, part 6: Versioning changes

This installment in the series on efficient data is on versioning changes in a table. The article is a re-post of a post I wrote in september on compressing slowly changing dimensions, although the concept does not only apply to dimensions – it can be used pretty much on any data that changes over time.

The idea is to “compress” a versioned table, so instead of just adding a date column for each version, you can compress multiple, sequential versions into a single row with a “from” date and a “to” date. This can significantly compress the size of the table.

Slowly changing dimensions (part 3)

In the third installment of the series on slowly changing dimensions, we’re going to tackle the question of how to manage accumulated fact aggregates in a solution that uses SCD 2 dimensions. While SCD 2 dimensions solve a lot of problems with slowly changing dimensions, accumulated values can still make a mess of the aggregate data.