Recently I noticed a very interesting question on Stackoverflow. A user wanted to add a particular column between two of the rows. He had a experience with MySQL so he was attempting following syntax. Following syntax will throw an error.
ALTER TABLE tablename ADD columnname INT AFTER anothercolumn
The above script will throw following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘AFTER’.
The reason is simple as the above syntax is valid in MySQL but is not valid in SQL Server. In SQL Server following syntax is valid:
ALTER TABLE tablename ADD columnname INT
However, a user wanted to add the column between two of the columns. SQL Server is relational engine. The order of the column should not matter in any of the T-SQL operations. It does not matter in most of the cases (except when the table is extra large, and it has many…