Readers often ask where I get the ideas for my columns. One plentiful wellspring of inspiration is the public newsgroups, where I frequently answer anywhere from 5 to 20 questions a day. If I find myself composing a long, detailed reply to a question, I wonder whether the topic is worth a column. In other cases, if I see similar questions repeatedly within a few days, I take that as a sign that many people want to know about the topic. Recently, I responded to questions on three different forums about what goes on inside SQL Server when it performs an ALTER TABLE operation. I realized I also had questions about which variations of ALTER TABLE require SQL Server to do more work than others do, so I rolled up my sleeves and started testing.
You can use an ALTER command in SQL Server 2000 and 7.0 to modify almost every kind of object that you can create with a CREATE command (e.g., database, procedure, view). For most objects, ALTER is a better choice than using DROP and CREATE to change an object, mainly because using ALTER maintains the object permissions. The new definition for procedures, functions, triggers, and views overwrites the old definition, but the permissions remain because you're not modifying the system table that stores permissions. Using the ALTER command to change a table is different than altering other kinds of objects—you can use ALTER TABLE to change just one table property without changing everything about the table.
SQL Server 2000 and 7.0 let you use the ALTER TABLE command to make many kinds of changes to a table, far more changes than earlier releases did. Before SQL Server 7.0, the only option available for the ALTER TABLE command was adding a new column, and then only if the new column allowed NULLs. Starting with SQL Server 7.0, ALTER TABLE lets you make any of the following changes inside one command:
- Adding new columns, which don't allow NULLs if you specify a default
- Adding new constraints
- Changing a column's data type or NULL property
- Dropping columns
- Dropping constraints
- Enabling or disabling foreign key or check constraints
- Enabling or disabling triggers
When you change an existing column's properties, you have to work within certain restrictions, which appear in SQL Server Books Online (BOL). One important thing to remember about modifying a column is that all changes must be compatible with existing data.
How Does It Do That?
Let's look at what SQL Server does internally when performing an ALTER TABLE command. SQL Server can carry out an ALTER TABLE command in any of three ways:
- SQL Server might need to change only metadata.
- SQL Server might need to examine all the existing data to make sure it's compatible with the change but then change only metadata.
- SQL Server might need to physically change every row.