Sql-server – Changing a column from NOT NULL to NULL – What’s going on under the hood

alter-tabledatabase-internalssql serversql-server-2008-r2

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).

Best Answer

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

enter image description here

And in the root page

enter image description here

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

enter image description here

and the rows in the upper level pages as below.

enter image description here

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

enter image description here

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

Related Question