Introduction

In the past week this topic came up in 3 different occasions and it seems there is lot of confusion around this topic. In this post I will try to delve into some details and a demo to show that it’s NOT true. The confusion here is, the operation has to touch each and every row of the table when you do ALTER TABLE ALTER COLUMN NOT NULL to NULL and that’s why it is expensive. Actually only some of the ALTER TABLE ALTER COLUMN operations are expensive and a lot of it depends on case by case.

The above myth can be busted in different ways and in this post we will use the sys.dm_tran_database_transactions.

Demo 1

In this demo, let’s create a table Customer with few NOT NULL columns and add some data. We will test if doing ALTER TABLE ALTER COLUMN NULL is really expensive and later see a different use case where this operation can be really expensive.

--Drop if exists and create the table
IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
	DROP TABLE dbo.Customer
GO
CREATE TABLE dbo.Customer (
	CustomerID	BIGINT NOT NULL
	, LoginName CHAR(100) NOT NULL
	, FirstName CHAR(100) NOT NULL
	, LastName  CHAR(100) NOT NULL
	, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerID)
)
GO

--Load data to this table
DECLARE @i BIGINT
SELECT @i = 0

SET NOCOUNT ON;
WHILE (@i < 10000)
BEGIN
	INSERT dbo.Customer (CustomerID, LoginName, FirstName, LastName)
		SELECT
			@i
			, 'LoginName' + CAST(@i AS VARCHAR(50))
			, 'FirstName' + CAST(@i AS VARCHAR(50))
			, 'LastName' + CAST(@i AS VARCHAR(50))

	SELECT @i = @i + 1
END
GO

Now, we have 10000 rows in the table and all of the columns are defined as NOT NULL. Let's say LastName column is NOT mandatory and we want to make it a NULLable column. The above table is using around 3.2 MB from EXEC sp_spaceused. If each and every row has to be updated then the amount of transaction log used should be high and comparable to the size of the table as Transaction log records every and every change.

--Do ALTER TABLE and check the amount of transaction log used
BEGIN TRAN
	ALTER TABLE dbo.Customer ALTER COLUMN LastName CHAR(100) NULL

	SELECT
		database_transaction_begin_lsn
		, database_transaction_last_lsn
		, database_transaction_log_bytes_used
		, *
	FROM sys.dm_tran_database_transactions
	WHERE database_id = DB_ID()

--Run the above without commit and later commit the transaction. Don't forget this.
COMMIT TRAN

The below picture shows how much transaction log space is used for the above operation. From the below, it's very clear that the above operation only took 596 bytes. This doesn't look like a very expensive operation.

Demo 2

In the next demo, we will look at a case where it is expensive to do ALTER TABLE ALTER COLUMN NULL. Let's change the table definition a bit and add a UNIQUE constraint on FirstName and Lastname columns. Note that this is a hypothetical case.

--Drop if exists and create the table
IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
	DROP TABLE dbo.Customer
GO
CREATE TABLE dbo.Customer (
	CustomerID	BIGINT NOT NULL
	, LoginName CHAR(100) NOT NULL
	, FirstName CHAR(100) NOT NULL
	, LastName  CHAR(100) NOT NULL
	, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerID)
	, CONSTRAINT UC_Customer_FirtName_Lastname UNIQUE (LastName, FirstName)
)
GO
--Load data to this table
DECLARE @i BIGINT
SELECT @i = 0

SET NOCOUNT ON;
WHILE (@i < 10000)
BEGIN
	INSERT dbo.Customer (CustomerID, LoginName, FirstName, LastName)
		SELECT
			@i
			, 'LoginName' + CAST(@i AS VARCHAR(50))
			, 'FirstName' + CAST(@i AS VARCHAR(50))
			, 'LastName' + CAST(@i AS VARCHAR(50))

	SELECT @i = @i + 1
END
GO

For this demo, let's change the NULLability for LastName column. We will measure the amount of transaction log used and see if it is an expensive operation. Note that the number of rows in this table is the same as the previous one.

--Do ALTER TABLE and check the amount of transaction log used
BEGIN TRAN
	ALTER TABLE dbo.Customer ALTER COLUMN LastName CHAR(100) NULL

	SELECT
		database_transaction_begin_lsn
		, database_transaction_last_lsn
		, database_transaction_log_bytes_used
		, *
	FROM sys.dm_tran_database_transactions
	WHERE database_id = DB_ID()

--Run the above without commit and later commit the transaction. Don't forget this.
COMMIT TRAN

From the below image, it's clear that this operation is very expensive. In fact this operation used almost 1.2 MB of transaction log for a 3.2 MB table. That is an expensive operation and we will look at why this one was expensive compared to the previous demo.

Internals

The reason Demo 2 is expensive because of NULL bitmap. If you are NOT familiar with NULL bitmap then please read these below posts from Paul Randal to get comprehensive information on this topic.

Misconceptions around null bitmap size

A SQL Server DBA myth a day: (6/30) three null bitmap myths

Size-of-data operations when adding and removing columns

Misconceptions around adding columns to a table

NULL bitmap will always be there in a table (heap or clustered table) irrespective of whether the table has NULLable columns or NOT. Note that we defined a UNIQUE constraint on LastName + FirstName in Demo 2 and UNIQUE constraint is enforced using a UNIQUE INDEX on those columns. NULL bitmap will NOT be present in a NON-CLUSTERED INDEX if all the columns part of the index definition are defined as NOT NULL. In our case both LastName + FirstName are defined as NOT NULL in the table and that's why NULL bitmap wasn't there initially. When we ALTERed the table definition, in this case the operation has to touch each and every row of the table and that's why it is an expensive operation.

Final Thoughts

From the above it is clear that all ALTER TABLE ALTER COLUMN NULL is NOT always expensive. The cases where it can be expensive depends on other factors and some of them are listed in the above Paul's articles. Don't forget to read them thoroughly. I hope this post made some sense and in the next post I will use a different method to bust this myth. Until then, stay out of trouble.