The Same Star in T-SQL

Everything in the V1 series so far has been PostgreSQL: SERIAL keys, BYTEA hashes, plpgsql triggers, pgcrypto's digest. That could make the inverted star schema look like a PostgreSQL trick — something that only works because of one engine's conveniences. So I ported V1 to SQL Server. The SQLServer folder is that port, and it corresponds to V1 exactly: same seven dimension types, same four computed measures, same SalesDim/SalesFact/CurrentSalesVersion, same SCD2 and time travel. None of V2 through V9 — just the foundation, rebuilt in T-SQL. This post is what changed in the crossing.

The mechanical translations

Most of the port is a dictionary lookup. The shapes are identical; the spellings differ.

• Keys. PostgreSQL's SERIAL PRIMARY KEY becomes INT IDENTITY(1,1) PRIMARY KEY.
• The hash column. BYTEA becomes BINARY(32) — SHA-256 is 32 bytes, and SQL Server lets you say so precisely.
• Small integers. SMALLINT for DimTypeId becomes TINYINT, which is genuinely better: dimension type ids never exceed a couple of dozen.
• The context column. CalculationContext was TEXT in V1; in T-SQL it is VARCHAR(MAX). (V4 later promotes it to JSONB on the PostgreSQL side, which has no clean SQL Server equivalent — another reason the port stays pinned at V1.)
• Booleans. BOOLEAN becomes BIT.
• Timestamps. TIMESTAMP / CURRENT_TIMESTAMP become DATETIME / GETDATE().

And the hash itself:

-- PostgreSQL
digest(v_concatenated, 'sha256')
-- SQL Server
HASHBYTES('SHA2_256', @concatenated)

Same algorithm, same 32-byte output, different function name. If the port had ended here it would be a boring find-and-replace. It did not.

Triggers stop being per-row

The interesting divergence is the trigger model. PostgreSQL gives you FOR EACH ROW triggers with NEW and OLD records — the insert trigger processes one sale at a time, naturally. SQL Server triggers are statement-level and set-based: they fire once per statement and hand you inserted and deleted pseudo-tables containing all affected rows. There is no per-row entry point.

So to reproduce the row-at-a-time explosion of a sale into seven dimension rows, the T-SQL trigger opens a cursor over inserted:

DECLARE sales_cursor CURSOR FOR
    SELECT Id, ProductCount, ProductPrice, Discount, SaleDate,
           SaleType, ProductId, CustomerId
    FROM inserted;

OPEN sales_cursor;
FETCH NEXT FROM sales_cursor INTO @Id, @ProductCount, ...;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- build @hash with HASHBYTES, insert seven SalesDim rows for @Id
    EXEC CreateSalesFacts @Id, @hash, ...;
    FETCH NEXT FROM sales_cursor INTO @Id, @ProductCount, ...;
END

CLOSE sales_cursor;
DEALLOCATE sales_cursor;

Cursors are the thing every SQL Server developer is trained to avoid, and here I reached for one deliberately — because the whole architecture is per-sale, and forcing it into a set-based rewrite would have obscured the correspondence with V1 that the port exists to demonstrate. It is the honest translation, not the idiomatic one. A production T-SQL version would push the explosion into a set-based INSERT ... SELECT against inserted; the cursor is a teaching artifact.

No MERGE, on purpose

If you have written SCD2 in SQL Server, you expect MERGE — it is the textbook tool for “update the expired row and insert the new one in a single statement.” The port does not use it. SCD2 here is the same two-step it is in PostgreSQL: an UPDATE that sets IsCurrent = 0 and stamps an expiry, then a separate INSERT of the new current row.

I kept the two-step for parity. The PostgreSQL trigger expires-then-inserts because that is the clearest expression of “never mutate history, supersede it,” and I wanted the T-SQL reader to see the same shape rather than a MERGE that folds both moves into one opaque statement. MERGE also carries enough well-documented footguns around triggers and concurrency that avoiding it in a reference implementation is defensible on its own.

String building reveals the engines

One small thing delighted me. PostgreSQL builds the hash input with STRING_AGG(... ORDER BY DimTypeId) — a single set-based aggregate with a guaranteed order. SQL Server, in the V1-era port, builds it the procedural way:

SELECT @concatenated = @concatenated + CAST(DimTypeId AS VARCHAR) + ':' + DimValue + '|'
FROM SalesDim
WHERE SalesReferenceId = @Id AND IsCurrent = 1
ORDER BY DimTypeId;
-- then trim the trailing '|' with LEFT/LEN

The SELECT @var = @var + ... accumulation is a SQL Server idiom that quietly depends on evaluation order, and it needs an explicit trailing-delimiter trim that STRING_AGG handles for free. Two engines, two philosophies: one hands you a declarative aggregate, the other hands you a variable and a loop. Both produce the identical 32-byte fingerprint, which is the point — the idea survives the translation intact.

What the port proved

Porting V1 to SQL Server was never about shipping a SQL Server product. It was a falsification test: if the inverted star schema only worked because of SERIAL and plpgsql and BYTEA, the port would fight me the whole way. It didn't. The dimension-as-rows storage, the SCD2 windows, the hash-based change detection, the pivot-on-read views, the time-travel function — all of it crossed cleanly. What changed were the mechanisms (cursors for row iteration, HASHBYTES for hashing, IDENTITY for keys), not the design.

That is the note the V1 series ends on. The foundation is engine-agnostic; everything that made it feel PostgreSQL-specific was incidental. Which frees the rest of the project to lean hard into PostgreSQL where it pays — JSONB, arrays, pg_cron, GIN indexes — knowing the shape would survive elsewhere if it had to.

Next, the V2 series takes the four NULL measures V1 left as a promissory note and finally computes them — and discovers that computing aggregates in a single fact table is harder to get right than it looks.