max_column_id_used: How Many Columns a Table Once Had

The column max_column_id_used holds the highest column ID a table ever used. That tells you how many columns the table once had. It lives in sys.tables, and it never goes down.

Column IDs Never Come Back

Every column of a table has a number, column_id. SQL Server gives the next free number to each new column. When you drop a column, its number leaves with it, and no later column takes it. The view sys.tables keeps the highest number ever given in max_column_id_used.

The demo creates a database named ColumnIdDemo and a table with five columns. Run the script on a test server.

IF DB_ID(N'ColumnIdDemo') IS NULL CREATE DATABASE ColumnIdDemo;
GO
USE ColumnIdDemo;
GO
DROP TABLE IF EXISTS dbo.Packages;
CREATE TABLE dbo.Packages (Col1 int, Col2 int, Col3 int, Col4 int, Col5 int);

A small procedure compares the highest ID with the number of columns that exist now. The difference is the number of columns that were dropped.

CREATE OR ALTER PROCEDURE dbo.ShowColumnHistory
AS
BEGIN
    SET NOCOUNT ON;
    SELECT t.name AS TableName,
           t.max_column_id_used AS EverCreated,
           c.CurrentColumns,
           t.max_column_id_used - c.CurrentColumns AS Dropped
    FROM sys.tables AS t
    CROSS APPLY (SELECT COUNT(*) AS CurrentColumns FROM sys.columns AS col WHERE col.object_id = t.object_id) AS c
    ORDER BY t.name;
END;

EXEC dbo.ShowColumnHistory;

The table has five columns, and the highest ID is 5. Now drop two columns, Col1 and Col5, and run the procedure again.

ALTER TABLE dbo.Packages DROP COLUMN Col1;
ALTER TABLE dbo.Packages DROP COLUMN Col5;

EXEC dbo.ShowColumnHistory;

Three columns remain, but max_column_id_used is still 5. The old value does not fall, so the gap of 2 equals the columns that were dropped. Now add a new column.

ALTER TABLE dbo.Packages ADD Col6 int NULL;

EXEC dbo.ShowColumnHistory;

The new column got ID 6, not ID 1. The highest ID rose to 6, and the table has four columns. That answers how many columns a table had in its history: six were created, and two were dropped.

The IDs Have Gaps

The column IDs now have holes. A script that treats column_id as a position gets wrong results.

SELECT c.name AS ColumnName, c.column_id AS ColumnID
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.Packages')
ORDER BY c.column_id;

The table shows IDs 2, 3, 4 and 6. There is no ID 1 or 5. If a script asks for the column with ID 5, it finds nothing. Use ROW_NUMBER() OVER (ORDER BY column_id) or the ordinal position below when you need a position.

The standard view INFORMATION_SCHEMA.COLUMNS does the numbering for you. Its ORDINAL_POSITION is a true position, counted from 1 with no gaps.

SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Packages'
ORDER BY ORDINAL_POSITION;

Col6 has position 4 here, and column ID 6 in the earlier result. Use the ordinal position to list columns for a person. Use the column ID when you join system views.

A Rebuild Does Not Reset It

A rebuild looks like a fresh start, but it does not renumber the columns. Changing a column’s type or length uses no new ID either. The gap counts dropped columns only.

ALTER TABLE dbo.Packages REBUILD;

EXEC dbo.ShowColumnHistory;

After the rebuild, the highest ID is still 6. Only a new table starts again at 1. To get clean numbers, create a new table, copy the data and swap the names.

1,100 Drops and No Error

A table can hold at most 1,024 columns without sparse columns. That limit counts the columns that exist, not the IDs. The script below adds and drops one column 1,100 times, in a loop with a fixed end. It then shows the history of both tables.

DROP TABLE IF EXISTS dbo.Churn;
CREATE TABLE dbo.Churn (KeepMe int NOT NULL);
GO
DECLARE @Round int = 0;
WHILE @Round < 1100
BEGIN
    ALTER TABLE dbo.Churn ADD Scratch int NULL;
    ALTER TABLE dbo.Churn DROP COLUMN Scratch;
    SET @Round += 1;
END;

EXEC dbo.ShowColumnHistory;

The table Churn has one column left, and its highest ID is 1,101. No error appeared, even though the number is above 1,024. The number does show how much the table was reshaped. A table with a large Dropped value has seen a lot of schema change.

Sparse and computed columns are counted too. A computed column takes an ID like any other column. Dropping it leaves a gap in the same way. The Dropped value counts every kind of column that left.

Why It Is Useful

The procedure above finds every table in a database that lost columns. Run it in a test or a production database and read the Dropped column. A large number points to a design that kept changing. Check whether the application still expects a column that was removed.

You could argue that this is trivia. It tells you nothing about which columns existed or when they left. That is right. SQL Server keeps no names for dropped columns here. For the names and the date, you need a DDL audit or a schema comparison. The value only says that something happened.

To drop several columns at once, read Drop Multiple Columns in One ALTER TABLE Statement. Every dropped column still keeps its ID forever.

What to Remember

The column max_column_id_used counts the columns a table ever had. Subtract the current number of columns to find how many were dropped. Never treat column_id as a position. Run the procedure on your own databases to see which tables changed most. When you finish with the demo, remove the database.

USE master;
GO
DROP DATABASE ColumnIdDemo;

A column ID is not a position, it is a receipt that SQL Server never reuses.

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