alter table renumbers column IDs when you drop a column from a table. Columns with IDs above the number of the dropped column move up one column ID to fill the gap that the dropped column leaves behind. For example, the titleauthor table contains these column names and column IDs:
Column Name |
au_id |
title_id |
au_ord |
royaltyper |
|---|---|---|---|---|
Column ID |
1 |
2 |
3 |
4 |
If you drop the au_ord column from the table:
alter table titleauthor drop au_ord
titleauthor now has these column names and column IDs:
Column Name |
au_id |
title_id |
royaltyper |
|---|---|---|---|
Column ID |
1 |
2 |
3 |
The royaltyper column now has the column ID of 3. The nonclustered index on both title_id and royaltyper are also rebuilt when au_ord is dropped. Also, all instances of column IDs in different system catalogs are renumbered.
You will not generally notice the renumbering of column IDs.
Because a table’s column IDs are renumbered
as columns are added and dropped, your applications should never
rely on them. If you have stored procedures or applications that
depend on column IDs, you must rewrite these so they access the
correct column IDs.
