Sql Server: Why We Can’t Add New Column in Between Existing Columns


Internally Sql Server maintains COLUMN_ID for each column of a table. If we have two columns in a table and we want to add one more. Newly added will have COLUMN_ID next to last created column i.e. if last COLUMN_ID was 2, newly added will have COLUMN_ID  3, and column will be place at the end of existing columns. That’s why we can’t add a column in between existing columns of a table. 
Though, order of column doesn’t matter, but still if we need it. We have to drop and recreate the table, with new column sequence.
Most of readers, by reading just title can have opinion that WE CAN insert new column in between existing columns. Yes it is possible through Sql Server Management Studio, but reality is different. AS SSMS follow same drop and recreate steps, i.e.:
      I.      Creates a temporary table according to existing structure of table
     II.      Shifts data from original table to temporary one
    III.      Drop original table
    IV.      Creates new table according to NEWLY GIVEN SEQUENCE of columns
     V.      Shifts data from temporary table to newly created table
    VI.      Drops temporary table
Its COLUMN_ID which is responsible to display columns in a fixed sequence whenever we execute SELECT * statement for a given table.
Advertisements

Posted on December 3, 2010, in Sql Server Internals, Tables. Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: