alter table renumbers column IDs when you drop a column from a table. Columns with IDs higher than the number of the dropped column move down 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.
Users generally will not 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, rewrite them so they access the correct column
IDs.
