Drop Columns Renumbers the Column ID

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.

Note: 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.