Adding NOT NULL Columns. Fast.

When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.

But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.

When I was a lad we had to do something like this:

ALTER TABLE dbo.Comments
ADD IsDeleted BIT NULL;
GO

UPDATE dbo.Comments
SET IsDeleted = 0;

ALTER TABLE dbo.Comments
ALTER COLUMN IsDeleted BIT NOT NULL;
GO

This can take a while. On my sample data with 3.8m records, this process burns 23 seconds of CPU, 448k logical reads, and increases storage used by 90mb.

Why bother with that when you can add the column with a default value:

ALTER TABLE dbo.Comments
ADD IsDeleted BIT NOT NULL
	CONSTRAINT DF_Comments_IsDeleted
    DEFAULT (0);

This operation on the same data set uses zero seconds of CPU, zero logical reads, and requires zero additional storage.

This wizardry is down to some metadata stored against the column:

SELECT
    c.[name],
	DefaultName = OBJECT_NAME(c.default_object_id),
	i.has_default,
	i.default_value
FROM
    sys.partitions p
	INNER JOIN sys.system_internals_partition_columns i
        ON p.partition_id = i.partition_id
	INNER JOIN sys.columns c
        ON p.[object_id] = c.[object_id]
        AND i.partition_column_id = c.column_id
WHERE
    p.object_id = OBJECT_ID('dbo.Comments')
	AND p.index_id IN (0, 1);

With this metadata in place, the engine doesn’t need to re-write any of the data. For existing records this default can be used when reading them.

That’s pretty cool in its own right, but it gets better. What will happen if the default is removed from the table?

ALTER TABLE dbo.Comments
DROP CONSTRAINT DF_Comments_IsDeleted;

Well, nothing really. I expected that removing the default constraint or querying the table might cause the records to be rewritten. But it didn’t.

The default value will persist even if the default is removed from the table. That’s due to the metadata being retained:

To recap – that’s a new column added, with a default value, and without needing to rewrite a single record. That’s some metadata magic right there.

So there you have it, supercharge your new columns by using default values rather than manually setting the value for existing records.