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.