SQL SERVER – How to Add Column at Specific Location in Table

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.

SQL SERVER - How to Add Column at Specific Location in Table columnorder

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)

SQL SERVER - How to Add Column at Specific Location in Table extracol1

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

SQL SERVER - How to Add Column at Specific Location in Table extracol2

Now, instead of using star let us use column names to retrieve the data.

SELECT FirstCol, ExtraCol, SecondCol
FROM ColumnName

SQL SERVER - How to Add Column at Specific Location in Table extracol3

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)

SQL Scripts, SQL Server, SQL Server Management Studio
Previous Post
SQLAuthority News – SQL Server Data Tools – Business Intelligence for Visual Studio 2012 – SQL Server 2012 Data-Tier Application Framework
Next Post
SQL SERVER – Avoid Using Function in WHERE Clause – Scan to Seek

Related Posts

92 Comments. Leave new

  • Rachel Fonseca (@Irachelfonseca)
    July 8, 2013 10:45 pm

    Wouldn’t it be simpler to create/recreate a view each time you add a new column to the table?

    Reply
  • how to insert rowvalue in a particular new column in sqlserver. is this possible to insert together new value in a particular column.
    Reply

    Reply
  • Nice..

    Reply
  • can we set the position when adding a new column to an existing table?

    Reply
  • David Marrero
    March 18, 2014 8:47 pm

    So when I want to quickly review data in several of the 200+ tables in the DB I need to type select top 20 x,y,z… instead of select top 20 * ? Really? One of the reasons for setting the order of the fields is so that we can QUICKLY review data. When trying to troubleshoot issues, I might issue 30-40 quick queries. Having the fields in a specific order helps make that process MUCH more efficient. Since it doesn’t matter (as you say), then it should be a minor issue to keep the fields in a useful order. No? Don’t suggest views. The fewer objects to manage, the better.

    Reply
  • It seems all these comments are in a cross fire. The original post talks about a feature from MySQL that is not available to SQL Server. The SSMS workaround is, as Pinal suggests, very expensive for large databases. The view workaround it is not such. It is general SQL knowledge. It is still useful, but it is not what the user was asking about.
    I think that those that advocate for “getting another job” if the column order is an issue for the DBA are missing the point like MSSQL team is when they reply to Erland Sommarskog connect item: https://docs.microsoft.com/en-us/collaborate/connect-redirect
    This is not about SQL performance but about human performance. We are not asking to take anybody’s tools away, just add the capability to do this metadata operation online like the MySQL folks are doing.

    Reply
  • how could i add a column as 1st column in a table which already exists?

    Reply
  • Knight Crawaler
    April 7, 2015 11:49 am

    OK. Conclusion is –> The first SQL statement in the blog { ALTER TABLE tablename ADD columnname INT AFTER anothercolumn }. MySQL is far ahead of MS SQL that why ORACLE bought SUN, else in sometime MySQL may overpass the ORACLE itself.

    Reply
    • Knight Crawaler
      April 7, 2015 11:50 am

      And guess what this blog is running on WordPress which uses MySQL
      x0x0

      Reply
    • David Marrero
      April 7, 2015 8:31 pm

      Funny. MySQL can’t come close to MSSQL. Try requesting multiple record sets some time, or try using CTE’s, or processing XML into or out of MySQL. Basic functionality. It’s a good DB, better than Oracle for a lot of basic things – but better than MSSQL – not even close.

      Reply
  • when looking for a solution to this problem myself, i ran upon this post/thread.

    my two cents: if a designer says that table column ordering is interesting, then it is. all that crap about it being necessary then look for a job? well, crap (and unhelpful too). and i agree with the other poster that using views, while a solution, adds more to-manage objects, not to mention doesn’t really address the question. (and seriously, a DB with hundreds of tables that must have hundreds of views to just satisfy a column ordering desire? a wacko suggestion, frankly.)

    while i agree that at the application level column ordering is not an issue (since ‘select * from’ should be basically illegal code), at the design and data investigation levels, it may be important, as someone rightly pointed out.

    so, since MS has overlooked this particular “feature”, and since no one else has suggested it here, my solution to this will be the following:

    Requirements:
    1) Insert a new column at a particular location between other columns of a table
    2) Retain all data currently existing in the table

    Solution:
    Since a script will be executed to make this change, i can:
    1) Save all data to a temporary table
    2) Drop and Re-Create the table, specifying the desired order of the columns
    3) Re-Insert the data from the Temp table in step 1 back into the new table: but since the columns now don’t match between the two, and, presuming there is a single PK identity column, we must explicitly specify which columns we are inserting data into
    N.B.) Step 3 can also be a good moment to fill our new column with its data, possibly based on logic, other data/tables/etc.
    5) Drop temp table

    DONE

    Reply
  • Simon Foster (@funkysi1701)
    April 24, 2015 7:55 pm

    The only reason I can think of to insert columns in specific location is if your database user interface is bad. Mine is and users access the table like it is a big excel sheet. What I need to do is give them a proper frontend and then it will not matter what order my columns are in as I can provide them with a view with the columns in any order I like. Until then I am going to have to keep dropping and creating my table when new columns are requested.

    Reply
  • Bramborovy Salat
    May 27, 2015 12:35 am

    I ran into a specific issue today. A large table (millions of rows) used during bulk import parse activity from huge text files (million rows per file) needed to be adjusted because the format for the text file now included 2 additional fields. The two new fields were present in the middle of the new text file and therefore the resulting table needed an insert of two fields. Adding them just at the end of the table was not a workable solution.

    In the end I added two fields to the end of the table using Alter Table Add Column statement and renamed the table to xxxtableOriginal and created a vew of the xxxtableOriginal with the order of the fields reflecting the two new fields present in the middle of the table.

    Reply
  • We have one table ORG having 10 columns. We have HIST table which is identicle to ORG table only 2 columns extra than ORG table. Now if I will add One column to ORG then there is no issue but if I want to add same in HIST then it gives message “Time out expired……..” though 7000 recs are there.

    Reply
    • have you tried using query rather than UI?

      Reply
      • Hi Dave,

        we need to create the column at specific location to avoid large data movements in production during automated deployment like with dacpac.

        dacpac generates scripts to create new table and move data if the column’s are not in the same order.

  • The very, very sad part about this whole discussion: MICROSOFT! Their software (SQL Server) should allow putting the new column wherever the developer wants to put it. SQL Server can then put that column wherever SQL Server wants to put the actual data. And then SQL Server can use it’s own “view” to always present the data to any user (user or developer) in the order requested by the developer when adding a column. If MySQL can do it, what in the world is wrong with the minds at Microsoft? I’m amazed that Microsoft allows their customers to spend millions of dollars in working on workarounds that Microsoft could put into the product itself and save us a huge amount of time, and therefore dollars!

    Reply
  • Hi Everyone,
    I need a solution by your side. Please suggest me a command which i could use in visual foxpro to add a column in between of existing columns.

    I am waiting for your reply group.

    Reply
  • So it exists in MySQL and that is acceptable but because there is no built in solution in SQL Server it’s somehow “bad”? Why shouldn’t we be able to order the columns how we want to make it more readable? Sure I can create a view with things ordered in a set way but why should I have to?

    Reply
  • Except Sql Server Management Studio also also takes care of foreign keys and the like. SSMS is awesome, it’s just a bit regrettable that all these in-built workarounds are needed instead of there being a low-level solution directly in the RDBMS…

    Still, you haven’t had it bad until you’ve tried Oracle, whose poor Java-based SSMS-wannabe lacks such features.

    Reply
  • Oops, this was in reply to this comment:

    Pinal Dave – April 14, 2015 7:17 am:
    You solution steps are same as done by SQL Server Management Studio

    Reply
  • I totally agree with ‘bob ama’ . I guess if someone asks if there is a direct way of creating a column in between existing columns of a table, then the answer should be “NO, SQL Server DOES NOT provide that capability in a single query/command like MySQL does. ” I guess that should be the starting point and then you can discuss the workarounds by using views or using design view etc or the bad idea of such a requirement. I guess people are scared of saying it openly that SQL Server does not provide that capability :-D
    I guess except for vicrauchAugust , ‘bob ama’ and UmairApril, everyone else were discussing need for such a requirement rather than admitting that SQL Server doesnt have it or they dont know it.

    Reply
  • I’m really surprised by the tone, and pure arrogance, of some people replying to a perfectly reasonable question. Sure, columns are unordered according to relational database theory. We all know that. But it is sometimes very convenient to have fields in a certain order (say, by keeping address fields together) to ease maintenance, especially when a table, which is perfectly normalized, contains hundreds of columns. Which is why SSMS supports this functionality out of the box, and products such as MySQL have it built in to their version of SQL. Microsoft and MySQL did not offer this functionality this because they had nothing else to do with their time. They did it because it was useful!

    Reply
  • Here is the solution to add a new column at specific position:

    ALTER TABLE Table_A DROP CONSTRAINT [PK_Table_A];

    CREATE TABLE [dbo].[Table_B](
    [Table_AID] [int] IDENTITY(1,1) NOT NULL,
    [Action] [varchar](50) NOT NULL,
    [ActionComments] [varchar](max) NULL, — THE NEW COLUMN
    [VendorID] [varchar](max) NULL,
    CONSTRAINT [PK_Table_A] PRIMARY KEY CLUSTERED
    (
    [Table_AID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    INSERT INTO [Table_B]
    ([Action]
    ,[ActionComments]
    ,[VendorID])

    SELECT [Action]
    ,NULL –No ActionComments for the old rows
    ,[VendorID]
    FROM [Table_A] (nolock);

    DROP TABLE [Table_A];

    EXEC SP_RENAME ‘Table_B’, ‘Table_A’;

    Reply

Leave a Reply