Friday, December 3, 2010

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.

1 comment:

All suggestions are welcome