Recently I noticed a very interesting question on Stackoverflow. A user wanted to add a particular column between two of the rows. He had an experience with MySQL so he was attempting following syntax. Following syntax will throw an error. Let us explore more about how to add column at specific locations in the table.
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 NULL columns it impacts the size of the table). In reality whenever user wants to add a column to the table, he/she should just the column and later retrieve the column in a specific order in the table using column names.
Bad Idea: Use SSMS to Add Column Between
I often see lots of developers using SQL Server Management Studio to insert column between two columns. It is indeed a bad idea because SQL Server Management Studio will under the hood do following process:
- Create a New Table with new columns
- Insert Data from previous table to new table
- Redo all the keys and constraints
- Drop old table
- Rename the new table to use the old table’s name
This process is indeed very bad as it is time consuming, resource wasting and performance degrading. During this process SQL Server will be not able to use the table and will create performance related issues with the server as well. Even though server has plenty of the resource, I particularly see no need of this process. The matter of the fact, there should no need to have column in a particular order as the best practice is to mention the name of the column name as described next.
Good Idea: Specify the Column Name
It is always a good idea to specify the name of the column in the T-SQL query (using * is indeed a bad idea but that is out of scope of this blog post).
Let us create a sample table with three columns.
CREATE TABLE ColumnName (FirstCol INT, SecondCol INT, ExtraCol INT)
Now let us try to see the table in the column in designer and you will see that ExtraCol is the last column.
Now let us retrieve the data using the following syntax (which is not a good idea).
SELECT * FROM ColumnName
Now, instead of using star let us use column names to retrieve the data.
SELECT FirstCol, ExtraCol, SecondCol FROM ColumnName
You can notice that now we have retrieved our data in our preferred way there is no need to insert the column in between two rows if you want them to be present there in the resultset.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
