SQL Server : Adding Column Error

Question

I am still finding my feet in SQL Server, and I am trying to do a simple column addition and it is throwing an error. I am trying to add the KinAdr3 column after the KinAdr2 column but it is throwing the following error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'AFTER'.

Below is the SQL statement I am trying to execute

ALTER TABLE TBL_MEDICAL_Patient
ADD KinAdr3 nVARCHAR(50) 
AFTER KinAdr2

Thanks in advance

[This might help](http://dba.stackexchange.com/questions/19536/add-a-new-column-and-define-its-position-in-a-table). — Felix Pamittan, Oct 19 '15 at 08:27
You don't need the "AFTER" clause.. And when modifying a table, you can't add a column in a specific position (*well, not easily*).. — Radu Gheorghiu, Oct 19 '15 at 08:27
The error speaks true - there is no `after` in `alter table`. You can only add it to the end — Nick.Mc, Oct 19 '15 at 08:28
@RaduGheorghiu - He is trying create a new column AFTER a existing column instead of last column — Pரதீப், Oct 19 '15 at 08:28
What if I want to add this column only after a specific column, If I don't put AFTER, will it not put this column at the end, which I don't want... — Rahul, Oct 19 '15 at 08:30

score 4 · Answer 1 · answered Oct 19 '15 at 08:28

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.

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).

For more details please see the SQL SERVER – How to Add Column at Specific Location in Table

SQL Server : Adding Column Error

1 Answers1