Vertical Partitioning: Moving Rarely Used Columns Off a Hot Table

Vertical partitioning moves rarely used columns into a side table, so your busiest query reads a narrower table. It can help a lot, help nothing, or just make your writes harder. The only way to know is to measure.

Why split a table at all

Picture a customer search screen. It shows an id and a code, and nothing else. But the customer table also carries a big notes column that nobody reads on that screen. Every search drags those notes through memory, page by page.

The idea is simple. Keep the hot columns in one table. Move the cold payload to a second table with the same key. Then the screen reads only the small one. Let me build both designs side by side so you can count.

Build the wide table and the split pair

The wide table holds 2000 customers with a 1000-character note each. The split pair has the same data. The detail table uses the same primary key as the hot table, plus a foreign key, so a note can never exist without its customer.

DROP TABLE IF EXISTS dbo.CustomerDetail, dbo.CustomerHot, dbo.CustomerWide;

CREATE TABLE dbo.CustomerWide (id int PRIMARY KEY, code varchar(20) NOT NULL, notes varchar(2000) NOT NULL);

INSERT dbo.CustomerWide (id, code, notes)
SELECT n, CONCAT('C', RIGHT(CONCAT('0000', n), 4)), REPLICATE('x', 1000)
FROM (SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS numbers;

CREATE TABLE dbo.CustomerHot (id int PRIMARY KEY, code varchar(20) NOT NULL);
CREATE TABLE dbo.CustomerDetail (
    id int PRIMARY KEY,
    notes varchar(2000) NOT NULL,
    CONSTRAINT FK_CustomerDetail_CustomerHot FOREIGN KEY (id) REFERENCES dbo.CustomerHot (id));

BEGIN TRANSACTION;
INSERT dbo.CustomerHot (id, code) SELECT id, code FROM dbo.CustomerWide ORDER BY id;
INSERT dbo.CustomerDetail (id, notes) SELECT id, notes FROM dbo.CustomerWide ORDER BY id;
COMMIT;

Measure the narrow read

Now run the same narrow question against both designs with STATISTICS IO on. Then ask how many pages each table uses. The scan count and logical reads are in the Messages tab.

SET STATISTICS IO ON;

SELECT COUNT_BIG(*) AS customers, MAX(code) AS last_code FROM dbo.CustomerWide;
SELECT COUNT_BIG(*) AS customers, MAX(code) AS last_code FROM dbo.CustomerHot;

SET STATISTICS IO OFF;

SELECT OBJECT_NAME(object_id) AS table_name, SUM(used_page_count) AS used_pages
FROM sys.dm_db_partition_stats
WHERE object_id IN (OBJECT_ID(N'dbo.CustomerWide'), OBJECT_ID(N'dbo.CustomerHot'), OBJECT_ID(N'dbo.CustomerDetail'))
GROUP BY object_id
ORDER BY table_name;

On my run, the wide table needed 288 logical reads and the hot table needed 8. Both queries returned the same 2000 customers. The page counts explain why: 288 pages for the wide table, 8 for the hot one, and 288 for the detail table. Your numbers will differ a little, but the shape will not.

Count the price of a full read

The split is not free. When a screen needs the notes too, it must join two tables. The next block reads every column from both designs.

SET STATISTICS IO ON;

SELECT MAX(code) AS last_code, SUM(LEN(notes)) AS note_chars
FROM dbo.CustomerWide;

SELECT MAX(h.code) AS last_code, SUM(LEN(d.notes)) AS note_chars
FROM dbo.CustomerHot AS h
JOIN dbo.CustomerDetail AS d ON d.id = h.id;

SET STATISTICS IO OFF;

Compare the lines in Messages. The join reads CustomerHot (8) and CustomerDetail (288), so it costs 296 reads. The wide table needed 288 on its own. If most of your queries need every column, the split makes them slower, not faster.

When the wide column is already off the row

Here is the surprise that catches people. A varchar(max) value too big for the row is stored off the row already. Reading the other columns never touches it. Let me build 300 customers with 20,000-character notes and run the same narrow read.

DROP TABLE IF EXISTS dbo.CustomerLob;

CREATE TABLE dbo.CustomerLob (id int PRIMARY KEY, code varchar(20) NOT NULL, notes varchar(max) NOT NULL);

INSERT dbo.CustomerLob (id, code, notes)
SELECT n, CONCAT('C', RIGHT(CONCAT('0000', n), 4)), REPLICATE(CAST('x' AS varchar(max)), 20000)
FROM (SELECT TOP (300) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS numbers;

SET STATISTICS IO ON;
SELECT COUNT_BIG(*) AS customers, MAX(code) AS last_code FROM dbo.CustomerLob;
SET STATISTICS IO OFF;

SELECT SUM(used_page_count) AS used_pages, SUM(in_row_data_page_count) AS in_row_pages
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.CustomerLob');

The table uses 756 pages, but only 3 of them hold the rows themselves. The narrow read costs 5 logical reads and zero LOB reads. Splitting this table would gain nothing. Check where your wide column really lives before you plan the move.

Keep the two tables in step

The price of the split is on the write side. Every insert, update and delete now touches two tables, and the order matters. Try to delete the customer first and the foreign key says no with error 547. Delete the detail first, inside one transaction.

BEGIN TRY
    DELETE dbo.CustomerHot WHERE id = 1;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number;
END CATCH;

BEGIN TRANSACTION;
DELETE dbo.CustomerDetail WHERE id = 1;
DELETE dbo.CustomerHot WHERE id = 1;
COMMIT;

SELECT (SELECT COUNT_BIG(*) FROM dbo.CustomerHot) AS hot_rows,
       (SELECT COUNT_BIG(*) FROM dbo.CustomerDetail) AS detail_rows;

Both tables end at 1999 rows. Every write path in your application needs the same care, which is the real cost. A covering index on the hot columns is another way to get a narrow read, so compare it before you split. The last block cleans up the demo tables.

DROP TABLE IF EXISTS dbo.CustomerDetail, dbo.CustomerHot, dbo.CustomerWide, dbo.CustomerLob;

Count the reads on your own table, then decide if the split earns its extra write work.

A narrow table is not free speed, it is a read and write tradeoff.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.