Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an INT). Just the nullability. The statement is as follows:

Alter Table dbo.Workflow Alter Column LineId Int NULL

The operation takes in excess of 10 before we stop it (we haven't even let it run to completion yet because it's a blocking operation and was taking too long). We'll probably copy the table to a dev server a test how long it actually takes. But, I'm curious if anyone knows what SQL Server is doing under the hood when converting from NOT NULL to NULL? Also, will affected indexes need to get rebuilt? The query plan generated doesn't indicate what's happening.

The table in question is clustered (not a heap).

As alluded to by @Souplex in the comments one possible explanation might be if this column is the first NULL-able column in the non clustered index it participates in.

For the following setup

CREATE TABLE Foo
  (
     A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     B CHAR(1) NOT NULL DEFAULT 'B'
  )

CREATE NONCLUSTERED INDEX ix
  ON Foo(B);

INSERT INTO Foo
            (B)
SELECT TOP 100000 'B'
FROM   master..spt_values v1,
       master..spt_values v2

sys.dm_db_index_physical_stats shows the non clustered index ix has 248 leaf pages and a single root page.

A typical row in an index leaf page looks like

And in the root page

Then running...

CHECKPOINT;

GO

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

SELECT Operation, 
       Context,
       ROUND(SUM([Log Record Length]) / 1024.0,1) AS [Log KB],
       COUNT(*) as [OperationCount]
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName = 'dbo.Foo.ix'
GROUP BY Operation, Context

Returned

+-----------------+--------------------+-------------+----------------+
|    Operation    |      Context       |   Log KB    | OperationCount |
+-----------------+--------------------+-------------+----------------+
| LOP_SET_BITS    | LCX_GAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_IAM            | 0.100000    |              1 |
| LOP_SET_BITS    | LCX_IAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | 8.700000    |              3 |
| LOP_FORMAT_PAGE | LCX_INDEX_LEAF     | 2296.200000 |            285 |
| LOP_MODIFY_ROW  | LCX_PFS            | 16.300000   |            189 |
+-----------------+--------------------+-------------+----------------+

Checking the index leaf again the rows now look like

and the rows in the upper level pages as below.

Each row has been updated and now contains two bytes for the column count along with another byte for the NULL_BITMAP.

Due to the extra row width the non clustered index now has 285 leaf pages and now two intermediate level pages along with the root page.

The execution plan for the

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

looks as follows

This creates a brand new copy of the index rather than updating the existing one and needing to split pages.

StackExchange Database Administrators Q#97738, answer score: 28