For years, I've wanted to compile a definitive list of which ALTER TABLE options fall into each of these categories. This column is a start at building that list. I ran tests in an attempt to determine which of the three types of alterations was happening.
The first type is by far the easiest to track down; you just need to set STATISTICS IO ON in Query Analyzer before issuing an ALTER TABLE command. If SQL Server is changing only metadata, you get no output back for STATISTICS IO. The ALTER TABLE command updates metadata only when SQL Server makes the following changes to a table: dropping a column or constraint, disabling a constraint, or disabling or enabling a trigger. In addition, SQL Server changes metadata only when you add a new column to a table without specifying a default value (the new column must allow NULLs in that case) or when you modify a column definition to allow NULL values. Listing 1 shows a quick way to verify the metadata-only behavior by creating a copy of the Pubs database's titles table, then changing the type column to allow NULLs and dropping the advance column. After setting STATISTICS IO ON and executing the ALTER TABLE commands, you'll see that this batch returns no statistics output.
The second and third possibilities are more difficult to distinguish because both require SQL Server to read all the pages in a table. I originally thought that if STATISTICS IO reported the same number of logical I/O operations as the number of pages in the table, SQL Server was only looking at the data. However, sometimes it's doing more. SQL Server might read each page only once even if it's changing every row. But consider the converse. If STATISTICS IO reports that SQL Server is accessing many more pages than are in the table, you can assume that SQL Server is doing more to the pages than reading all the rows.
So, how can you tell whether the table data (and not just the metadata) has changed? One way is to use fn_dblog() to look at the number of log records, as I did in my June 2003 column, "Inside Recovery Models," InstantDoc ID 38510. If SQL Server is only examining each row without making any changes, the log should contain few new records. However, if you're running tests in a database that other people are using concurrently, their operations will affect the log, and checking the number of new log records might not be useful. An alternative method is to examine the data pages, as I did in my April 2001 column, "The Fill-Factor Truth," InstantDoc ID 19851. That article contains details about using the DBCC IND command, which tells you which pages belong to a table, and using the DBCC PAGE command to see the actual data in the table.
Testing, Testing
Listing 2 creates a copy of the Northwind database's Orders table and builds a clustered index on the new table. It then uses DBCC IND to report all the pages in the table. You need to find the page that has no value for previous page but has a next page value—that will be the first page of the table. Use that page number in place of XXX in the DBCC PAGE command. Now let's look at five tests I ran to see what SQL Server does when it performs different types of ALTERs.
The first test changes a data type from int to smallint in NewOrders:
ALTER TABLE NewOrders
ALTER COLUMN EmployeeID
smallint
GO
And the following command changes a column in NewOrders to not allow NULLs:
ALTER TABLE NewOrders
ALTER COLUMN EmployeeID
smallint NOT NULL
GO
In both of these tests, note that SQL Server needs to scan the table because it must ensure, in the first case, that no values are too big for a smallint and, in the second case, that no rows already contain NULLs. If any rows contain unacceptable data, the ALTER TABLE command will fail. Using DBCC PAGE to examine the page shows that the ALTER command made no changes on the page; it just changed the metadata describing the table structure.
Prev. page
1
[2]
3
next page 
