Slowly Changing Dimensions in SQL Server: SCD Types 0 to 6

The Problem in One Example

Dimensions describe things that are supposed to be stable: customers, products, employees, cost centres. In practice they are stable for a while and then change. A customer relocates, a product is reclassified, a sales rep moves to another region. The fact table already has millions of rows pointing at that dimension, and now you have to decide what "pointing at it" means after the change.

There are only three fundamental answers, and everything else is a combination of them:

Ignore the change. The dimension keeps the original value forever.
Overwrite the value. History disappears, every report reflects today's truth.
Keep both. The old value stays attached to old facts, the new value applies going forward.

Kimball numbered these patterns, and the numbers stuck. Types 0, 1 and 2 are the fundamentals; 3, 4, 5 and 6 are variations you reach for when the fundamentals do not fit. What follows is one worked example per type against the same dimension, plus a decision table at the end.

The Common Setup

A source system that only ever exposes the current state, which is the normal case and the whole reason SCD handling exists:

-- What the source hands you: current state only, no history
CREATE TABLE stg.Customer (
  CustomerID    INT           NOT NULL,   -- business key, stable
  CustomerName  NVARCHAR(100) NOT NULL,
  City          NVARCHAR(60)  NOT NULL,
  Segment       NVARCHAR(20)  NOT NULL,   -- 'Retail', 'Corporate', ...
  TaxID         NVARCHAR(20)  NULL
);

-- Monday
INSERT INTO stg.Customer VALUES (4711, N'Contoso GmbH', N'Berlin', N'Retail', N'DE123456789');
-- Tuesday, after the move and an upgrade
-- (4711, N'Contoso GmbH', N'Munich', N'Corporate', N'DE123456789')

One business key, two attributes changed, one attribute that must never change. Every SCD type below is a different answer to the same input.

Type 0: Retain Original

The column is written once at insert time and never updated again, no matter what the source says. This is not laziness, it is an explicit rule: some attributes are supposed to be immutable, and a change in the source means bad data rather than new truth.

-- Type 0 columns are simply excluded from every UPDATE
UPDATE d
   SET d.CustomerName = s.CustomerName,
       d.City         = s.City
       -- OriginalTaxID and DateFirstOnboarded are deliberately absent
FROM dim.Customer AS d
JOIN stg.Customer AS s ON s.CustomerID = d.CustomerID;

Typical Type 0 attributes: original signup date, original contract number, date of birth, the credit score at onboarding time. If one of them changes in the source, you want an alert, not a silent overwrite.

Use Type 0 when the attribute is historically fixed by definition, and a change means a data quality problem worth investigating rather than an update worth applying.

Type 1: Overwrite

The simplest and by far the most common. The old value is gone, and every report, including reports over facts from three years ago, immediately shows the new value.

CREATE TABLE dim.Customer (
  CustomerKey   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  -- surrogate key
  CustomerID    INT           NOT NULL UNIQUE,           -- business key
  CustomerName  NVARCHAR(100) NOT NULL,
  City          NVARCHAR(60)  NOT NULL,
  Segment       NVARCHAR(20)  NOT NULL
);

UPDATE d
   SET d.CustomerName = s.CustomerName,
       d.City         = s.City,
       d.Segment      = s.Segment
FROM dim.Customer AS d
JOIN stg.Customer AS s ON s.CustomerID = d.CustomerID
WHERE d.CustomerName <> s.CustomerName
   OR d.City         <> s.City
   OR d.Segment      <> s.Segment;   -- only touch rows that really changed

One row per customer, forever. Fact rows never need to move, key counts stay small, joins stay cheap. The price is that the question "what did this report look like last quarter" becomes unanswerable: rerun it today and the numbers regroup themselves.

Use Type 1 when the change is a correction rather than an event: a typo in a name, a fixed spelling, a re-formatted phone number. Nobody wants a second version of a customer because someone removed a trailing space.

Type 2: Add a New Row (Versioning)

The workhorse. A change closes the existing row and opens a new one with a new surrogate key. Old fact rows keep pointing at the old key and therefore keep the old attribute values; new facts get the new key.

CREATE TABLE dim.Customer (
  CustomerKey   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  -- surrogate key, per VERSION
  CustomerID    INT           NOT NULL,                  -- business key, repeats
  CustomerName  NVARCHAR(100) NOT NULL,
  City          NVARCHAR(60)  NOT NULL,
  Segment       NVARCHAR(20)  NOT NULL,
  ValidFrom     DATE          NOT NULL,
  ValidTo       DATE          NOT NULL,                  -- '9999-12-31' while current
  IsCurrent     BIT           NOT NULL,
  RowHash       BINARY(32)    NOT NULL                   -- change detection
);

-- Exactly one current version per business key, enforced by the engine
CREATE UNIQUE INDEX UX_dimCustomer_Current
  ON dim.Customer (CustomerID) WHERE IsCurrent = 1;

CREATE INDEX IX_dimCustomer_BK_Valid
  ON dim.Customer (CustomerID, ValidFrom, ValidTo) INCLUDE (CustomerKey);

After Tuesday's load the dimension holds two rows for one customer:

CustomerKey	CustomerID	City	Segment	ValidFrom	ValidTo	IsCurrent
8801	4711	Berlin	Retail	2024-01-15	2026-09-07	0
9142	4711	Munich	Corporate	2026-09-08	9999-12-31	1

Loading It: Expire, Then Insert

Two set-based statements in one transaction. No cursor, no row-by-row component, and no dependency on MERGE:

DECLARE @Effective DATE = CAST(SYSUTCDATETIME() AS DATE);

-- Hash only the attributes that are tracked as Type 2
SELECT
  CustomerID, CustomerName, City, Segment,
  HASHBYTES('SHA2_256',
    CONCAT(ISNULL(CustomerName, N'~NULL~'), N'|',
           ISNULL(City,         N'~NULL~'), N'|',
           ISNULL(Segment,      N'~NULL~'))) AS RowHash
INTO #src
FROM stg.Customer;

BEGIN TRANSACTION;

  -- 1. Close the current version of every changed business key
  UPDATE d
     SET d.ValidTo   = DATEADD(DAY, -1, @Effective),
         d.IsCurrent = 0
  FROM dim.Customer AS d
  JOIN #src AS s ON s.CustomerID = d.CustomerID
  WHERE d.IsCurrent = 1
    AND d.RowHash <> s.RowHash;

  -- 2. Insert a new version for everything without a current row.
  --    That is exactly: the rows just expired, plus genuinely new customers.
  INSERT INTO dim.Customer
        (CustomerID, CustomerName, City, Segment, ValidFrom, ValidTo, IsCurrent, RowHash)
  SELECT s.CustomerID, s.CustomerName, s.City, s.Segment,
         @Effective, '9999-12-31', 1, s.RowHash
  FROM #src AS s
  WHERE NOT EXISTS (
    SELECT 1 FROM dim.Customer AS d
    WHERE d.CustomerID = s.CustomerID AND d.IsCurrent = 1
  );

COMMIT TRANSACTION;

⚠ Hash the values carefully. CONCAT silently turns NULL into an empty string, so without a sentinel ('AB', NULL) and ('A', 'B') hash identically and a real change goes undetected. Use an explicit delimiter plus a NULL placeholder, always hash the columns in the same order, and be aware that trailing spaces in VARCHAR comparisons behave differently from trailing spaces in a hash.

Assigning the Right Key at Fact Load Time

This is the part that people skip and regret. The fact load has to look up the version that was valid when the event happened, not the version that is current now:

INSERT INTO fact.Sales (OrderDate, CustomerKey, Amount)
SELECT o.OrderDate,
       ISNULL(d.CustomerKey, -1),   -- -1 = the Unknown member
       o.Amount
FROM stg.Orders AS o
LEFT JOIN dim.Customer AS d
       ON d.CustomerID = o.CustomerID
      AND o.OrderDate BETWEEN d.ValidFrom AND d.ValidTo;

Because the fact stores the surrogate key of a specific version, reporting needs no interval logic at all. A plain join returns the attribute values as they were on the day of the sale, which is the entire point of Type 2.

⚠ Pick one interval convention and never mix it. The example above uses closed DATE intervals, so the old row ends the day before the new one starts and BETWEEN works. If you version at datetime2 grain, switch to half-open intervals (ValidTo = the new ValidFrom, queried with ValidFrom <= @t AND @t < ValidTo). Mixing both conventions in one warehouse produces facts that match two versions or none.

Use Type 2 when the change is a real event and history must stay accurate: territory reassignments, customer segment changes, price list versions, org structure changes. Anything where "as it was then" is a legitimate reporting requirement.

Type 3: Add a Column for the Previous Value

Keeps exactly one step of history, in extra columns rather than extra rows. There is still one row per customer.

ALTER TABLE dim.Customer ADD
  PreviousSegment  NVARCHAR(20) NULL,
  SegmentChangedOn DATE         NULL;

UPDATE d
   SET d.PreviousSegment  = d.Segment,
       d.Segment          = s.Segment,
       d.SegmentChangedOn = CAST(SYSUTCDATETIME() AS DATE)
FROM dim.Customer AS d
JOIN stg.Customer AS s ON s.CustomerID = d.CustomerID
WHERE d.Segment <> s.Segment;

Now a report can group by current segment and by previous segment side by side without any interval joins, which is genuinely useful during a reorganisation. The limitation is hard: change the segment twice and the first value is gone for good.

Use Type 3 when there is a one-off restructuring and the business wants to see both views at once, for example "sales by new region" and "sales by old region" during the year of a territory redesign. It is a poor fit for anything that changes repeatedly.

Type 4: Separate History Table or Mini-Dimension

Type 4 splits volatility away from the main dimension. It comes in two flavours that get the same number.

Flavour A: Current Table Plus History Table

-- Slim, fast, always current: this is what reports join to
CREATE TABLE dim.Customer (
  CustomerKey  INT IDENTITY(1,1) PRIMARY KEY,
  CustomerID   INT NOT NULL UNIQUE,
  CustomerName NVARCHAR(100) NOT NULL,
  City         NVARCHAR(60)  NOT NULL,
  Segment      NVARCHAR(20)  NOT NULL
);

-- Every previous state, queried only when someone asks for history
CREATE TABLE dim.Customer_History (
  CustomerHistKey INT IDENTITY(1,1) PRIMARY KEY,
  CustomerID      INT NOT NULL,
  CustomerName    NVARCHAR(100) NOT NULL,
  City            NVARCHAR(60)  NOT NULL,
  Segment         NVARCHAR(20)  NOT NULL,
  ValidFrom       DATE NOT NULL,
  ValidTo         DATE NOT NULL
);

Everyday queries stay on a compact one-row-per-customer table; the audit trail lives next door. The catch: facts point at the current row, so historical accuracy of a fact is not preserved. This is history for lookup, not history for reporting.

Flavour B: Mini-Dimension for Rapidly Changing Attributes

The classic Type 2 failure mode is an attribute that changes constantly. Track a customer's income band, age band and risk score as Type 2 and you get dozens of versions per customer per year, which multiplies the dimension without adding analytical value. The fix is to move those attributes into their own small dimension of all possible combinations and let the fact reference it directly:

CREATE TABLE dim.CustomerDemographics (
  DemographicsKey INT IDENTITY(1,1) PRIMARY KEY,
  AgeBand         NVARCHAR(12) NOT NULL,   -- '18-24', '25-34', ...
  IncomeBand      NVARCHAR(12) NOT NULL,   -- 'to 30k', '30-60k', ...
  RiskScoreBand   NVARCHAR(12) NOT NULL,
  CONSTRAINT UQ_Demographics UNIQUE (AgeBand, IncomeBand, RiskScoreBand)
);

-- The fact carries both keys
CREATE TABLE fact.Sales (
  SalesKey        BIGINT IDENTITY(1,1) PRIMARY KEY,
  OrderDate       DATE   NOT NULL,
  CustomerKey     INT    NOT NULL REFERENCES dim.Customer(CustomerKey),
  DemographicsKey INT    NOT NULL REFERENCES dim.CustomerDemographics(DemographicsKey),
  Amount          DECIMAL(19,4) NOT NULL
);

The mini-dimension has a few hundred rows and never grows with customer count. Each fact captures the band combination at the moment of the transaction, so history is exact, and dim.Customer stays stable.

Use Type 4 when either the dimension is queried far more often than its history is (flavour A), or a handful of attributes change so frequently that Type 2 would explode the row count (flavour B).

Type 5: Mini-Dimension Plus a Type 1 Pointer

Type 4 flavour B has one gap: you can answer "what were the bands at transaction time" from the fact, but you cannot answer "what are this customer's bands right now" without touching the fact table. Type 5 adds a Type 1 overwritten pointer to the base dimension:

ALTER TABLE dim.Customer ADD
  CurrentDemographicsKey INT NULL
    REFERENCES dim.CustomerDemographics(DemographicsKey);

-- Overwritten on every load, no new versions created
UPDATE d
   SET d.CurrentDemographicsKey = m.DemographicsKey
FROM dim.Customer AS d
JOIN stg.CustomerBands AS s ON s.CustomerID = d.CustomerID
JOIN dim.CustomerDemographics AS m
  ON m.AgeBand       = s.AgeBand
 AND m.IncomeBand    = s.IncomeBand
 AND m.RiskScoreBand = s.RiskScoreBand;

Now both questions are answerable: the fact gives you the historical bands, the pointer gives you today's, and neither adds a row to the customer dimension.

Type 6: The Hybrid (1 + 2 + 3)

The number is literally the sum, and it is the most useful of the compound types. The dimension is versioned like Type 2, but it also carries a set of "current" columns that are overwritten on every version of the business key, Type 1 style.

CREATE TABLE dim.Customer (
  CustomerKey    INT IDENTITY(1,1) PRIMARY KEY,
  CustomerID     INT           NOT NULL,
  CustomerName   NVARCHAR(100) NOT NULL,
  Segment        NVARCHAR(20)  NOT NULL,   -- Type 2: value as of this version
  CurrentSegment NVARCHAR(20)  NOT NULL,   -- Type 1: today's value, on every row
  ValidFrom      DATE NOT NULL,
  ValidTo        DATE NOT NULL,
  IsCurrent      BIT  NOT NULL
);

-- After the Type 2 expire/insert has run, push today's value across all versions
UPDATE d
   SET d.CurrentSegment = s.Segment
FROM dim.Customer AS d
JOIN stg.Customer AS s ON s.CustomerID = d.CustomerID
WHERE d.CurrentSegment <> s.Segment;   -- every version of that business key

One join, two legitimate answers to the same question:

-- Revenue by the segment that applied at the time of sale
SELECT d.Segment, SUM(f.Amount)
FROM fact.Sales AS f
JOIN dim.Customer AS d ON d.CustomerKey = f.CustomerKey
GROUP BY d.Segment;

-- The same historic revenue, regrouped under today's segmentation
SELECT d.CurrentSegment, SUM(f.Amount)
FROM fact.Sales AS f
JOIN dim.Customer AS d ON d.CustomerKey = f.CustomerKey
GROUP BY d.CurrentSegment;

Use Type 6 when the business genuinely needs both perspectives, which happens more often than people admit: sales analysis "as it was" for accountability, and "as if today" for comparing performance across a reorganisation.

A Note on System-Versioned Temporal Tables

SQL Server 2016 and later can version a table automatically, and it is tempting to declare the SCD problem solved:

ALTER TABLE dbo.Customer ADD
  SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
    DEFAULT SYSUTCDATETIME(),
  SysEndTime   datetime2 GENERATED ALWAYS AS ROW END   NOT NULL
    DEFAULT '9999-12-31 23:59:59.9999999',
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE dbo.Customer
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Customer_History));

SELECT * FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2026-03-01'
WHERE CustomerID = 4711;

It is excellent for auditing, and it is not a Type 2 dimension. Temporal tables record transaction time, meaning when the row was changed in this database, not valid time, meaning when the fact was true in the real world. A load that arrives three days late is stamped with the load date. History rows also get no surrogate keys, so no fact table can point at a version. Treat temporal tables as an excellent source of change data feeding your Type 2 load, not as a replacement for it.

Which One When

Type	On change	History	Dimension growth	Pick it when
0	Nothing, source value ignored	Original value only	None	The attribute is immutable by definition and a change means bad data
1	Overwrite in place	None	None	The change is a correction; nobody should ever see the old value
2	Expire old row, insert new version	Full, and fact-accurate	One row per change	History matters for reporting and the attribute changes at a sane rate
3	Shift value into a previous-value column	One step back only	None (extra columns)	A one-off restructuring where both views are needed side by side
4	Move old state to a history table, or bands to a mini-dimension	Full, but for lookup (A) or via the fact (B)	None in the main dimension	The dimension must stay slim, or attributes change too fast for Type 2
5	Type 4B plus an overwritten current-key pointer	Historic via fact, current via pointer	None	You need both today's bands and the bands at transaction time
6	New version plus current-value columns updated on all versions	Full, both perspectives	One row per change	Reporting must support "as it was" and "as if today" from one join

The Decision Is Per Column, Not Per Table

The single most common design mistake is declaring "dim.Customer is a Type 2 dimension" and applying it to all 40 columns. It is normal and correct for one dimension to mix types:

DateFirstOnboarded: Type 0, never changes.
CustomerName, EmailAddress: Type 1, corrections nobody wants versioned.
City, SalesRegion, Segment: Type 2, real events that reports must respect.
RiskScoreBand: Type 4B, changes monthly and would flood the dimension.

Write that classification down as part of the model, column by column, before you write the load. It is the document that determines what the hash in your Type 2 load covers: a Type 1 column must be excluded from the hash, otherwise every typo correction spawns a new version.

Mistakes That Cost Real Money

Putting the business key in the fact table. If fact.Sales stores CustomerID instead of CustomerKey, Type 2 is structurally impossible; the join always finds the current version. This is nearly unfixable once facts have accumulated.
No unknown member. A fact whose dimension row has not arrived yet joins to nothing, and an inner join silently drops revenue. Seed a -1 row and default to it.
Hashing Type 1 columns. Include a phone number in the change hash and every trivial correction produces a new version. Row counts explode, and nobody can explain why.
A non-idempotent load. Re-run a partially failed Type 2 load and you get two current rows or overlapping intervals. Wrap expire and insert in one transaction, and let the filtered unique index on IsCurrent = 1 stop the damage at the engine level.
Mixing NULL and 9999-12-31 as the open end date. Half your queries then need IS NULL handling that the other half does not have. Pick the sentinel date; it keeps BETWEEN and index seeks working.
Hard-deleting from the source. A customer disappearing from the feed is not a reason to delete dimension rows that facts still reference. Expire the row and flag it as deleted.

Two Checks Worth Running After Every Load

-- 1. More than one current version per business key
SELECT CustomerID, COUNT(*) AS CurrentRows
FROM dim.Customer
WHERE IsCurrent = 1
GROUP BY CustomerID
HAVING COUNT(*) > 1;

-- 2. Gaps or overlaps in the validity timeline
SELECT CustomerID, ValidFrom, ValidTo, PrevValidTo
FROM (
  SELECT CustomerID, ValidFrom, ValidTo,
         LAG(ValidTo) OVER (PARTITION BY CustomerID ORDER BY ValidFrom) AS PrevValidTo
  FROM dim.Customer
) AS x
WHERE PrevValidTo IS NOT NULL
  AND ValidFrom <> DATEADD(DAY, 1, PrevValidTo);

Both should return zero rows. If they do not, stop the downstream refresh rather than publishing numbers that will need retracting.

How to Load It

Everything above is set-based on purpose. The temptation is to reach for the SSIS Slowly Changing Dimension wizard because it looks like it does exactly this, but it generates row-by-row OLE DB Command calls and falls apart well before a million rows. That is a separate story, told in Why to Avoid the Slowly Changing Dimension Component in SSIS.

If you prefer a single statement over the expire-then-insert pair, the MERGE-based Type 2 pattern walks through the OUTPUT-clause trick that makes it work, and the caveats that come with it. For the surrounding model, fact and dimension tables in a star schema and building a date dimension table fill in the rest of the picture.

Checklist

☐ Classify every dimension column as Type 0, 1, 2, 3 or 4 before writing the load
☐ Facts store surrogate keys only; the business key never leaves the dimension
☐ Type 2 change detection hashes exactly the Type 2 columns, with NULL sentinels and a fixed column order
☐ A filtered unique index enforces one current version per business key
☐ One interval convention across the whole warehouse, closed or half-open, never both
☐ Fact loads resolve the key by event date, not by IsCurrent = 1
☐ An unknown member (-1) exists in every dimension and is the default for unresolved keys
☐ Expire and insert run in one transaction, and the load is safe to re-run
☐ The duplicate-current and gap/overlap checks run after every load and block publication on failure
☐ Rapidly changing attributes go to a mini-dimension instead of multiplying versions

The Bottom Line

There is no default SCD type, and picking one for a whole table is how warehouses end up with either no history at all or ten million dimension rows nobody can explain. Ask one question per column: if this value changes, should last year's report change with it? Yes means Type 1, no means Type 2, "it depends who is asking" means Type 6, and "it should never change at all" means Type 0. Get that classification right and the load code is fifteen lines of straightforward SQL. Get it wrong and you will be explaining to a controller why a signed-off quarterly report now shows different numbers.

Slowly Changing Dimensions in SQL Server: SCD Types 0 to 6 with Examples