alter table add column at specific position in sql server

edx
edx
90 Points
30 Posts

Is it possible to add a column to a table at a specific ordinal position in SQL Server?

For instance, our tables always have CreatedDate, CreatedBy, LastModifiedDate, LastModifiedBy columns at the "end" of each table definition? I'd like the new column to show up in SSMS above these columns.

Views: 13022
Total Answered: 2
Total Marked As Answer: 1
Posted On: 11-Jun-2017 21:50

Share:   fb twitter linkedin
Answers
Rahul Maurya
Rahul M...
Moderator
1264 Points
222 Posts
         

Unfortunately you can't.

If you really want them in that order you'll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.

Posted On: 13-Jun-2017 22:19
Smith
Smith
Participant
586 Points
138 Posts
         

I agree too, SQL Server does not care about the ordinal position. If you want to create the table with a specific column order or rearrange order, you will have to recreate the table and dump data out and in to the new table. Why do you care about the ordinal position, relying on this is not considered as a best practice.

Aside  from that we could declare a view with the desired column order as a way of grouping logically related columns together.

Posted On: 02-Oct-2019 01:24
 Log In to Chat