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 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 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 following syntax (which is not a good idea).

SELECT *
FROM ColumnName
GO

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

SELECT FirstCol, ExtraCol, SecondCol
FROM ColumnName
GO

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.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

39 thoughts on “SQL SERVER – How to Add Column at Specific Location in Table

  1. Hi ,the second idea is a very general idea of data retrieval that dont qualify the post. as i think the feature of mysql column adding is not available in MsSQL server, if this(mysql) type of idea is available in Ms Sql please post .

  2. Although I agree that you should not use “SELECT *” in operational use, this completely sidesteps why I would want a column in a specific place: it’s not for the machine executing the query, it’s for the human who’s searching for data and is building the final query.
    By grouping columns logically together, it’ll be easier for him/her to find what he/she wants. Instead of risking missing essential bits because columns are all over the place.

    • @GJKuijn : Agreed, this is not a valid solution.

      Also, if someone is capable of altering a table, chances are they already know you can specify the order of columns in a SELECT query, if they don’t, they should not be using SQL.

  3. But sir, we haven’t really ADDED the column at a specified location right? we are just retrieving the extra column at the desired location… Do we have a provision to INSERT a new column in between other existing columns using MS SQL Server?

    Thank you.

      • Because *I* want to see it there so it’s easier for me and other developers to understand the intent of the data structure. Why unnecessarily increase the time and cost of writing queries?

        • If *you* want to re-order columns, then *you* arrange for the downtime during a maintenance window and explain to your business users that you need columns in a certain order in order to avoid the “time and cost of writing queries”.

          Frankly, if you are unable to write queries from a data dictionary and ERD; and require the database, as physically implemented, to be modified to suit your personal preferences; start looking for a job. You will simply be too expensive to keep as an employee or contractor.

        • Then *you* can arrange for downtime during a maintenance window to export the data from the table, re-order the columns and import the data, then re-creating the indexes and statistics associated with the table.

          Frankly, you can write queries regardless of the order of the columns. There’s no additional “time or cost” with the columns in an arbitrary order.

          Your personal preference would cost both time and money to the owner of the database. There is no return on that investment.

          Expect your request for a maintenance window to be denied (and rightly so)

      • Hi pinaldave,

        we compared two databases for schema change, we got script.
        we run the same script in other database the column added in last position of table. but in first database it is in 4th position…

        Problem is , we take the table data from first database and add it into second database table the data split into wrong columns because of mismatch order.

        Is there any way to add column to a table desired position using script?

        Thanks & Regards,
        Srinivas.

      • Yes, I must add a new column after a specific column in my table since the input data is coming from remote system in that order. It’s a direct download to this table thru an app, so columns should be in a particular order. I don’t have room for remapping the columns. Either I should add new column in a particular order or create a view(I don’t really prefer this path just for aligning columns) to match the input order. Please help me how to add a column in SQL in after a specific column.

  4. Hi,

    We can achieve this,

    - Open table in Design view
    - Select column you want to move (Click on square sign left side of
    the column name )
    - Move column (Drag using mouse cursor)
    - Save

    done :)

  5. It is my impression people develop the habit of using SSMS to add columns due to a lack of knowledge and/or exposure to DDL. The bad effects outlined in your post are not as evident when working with databases sized in MB, rather that GB and TB. Or people make changes to databases/tables with no data present. Adding a column and clicking “save” doesn’t appear to slow things down. Try this on a table with a few million rows and life is not good.

  6. I hope my statement is in scope with this blog. Although I agree that adding a column in a specific location, rather than adding it as the last column, is advisable and makes sense, I feel GJKuijn’s statement has merit. When I am perusing an unfamiliar database for data elements it is easier to miss a piece of information when the data elements in a table are not logically grouped together. I would consider adding a new column in a specific location in a table if that table is relatively small and the adding of the new column can be done after hours. However in counter point, most newly created tables I have seen end with created by and updated by columns. Any columns that follow these columns are easily identified as columns added after the initial creation of the table; information that can also be useful to developers.

  7. Well, it depends on what the table and column are used for. For example, if the table is used to perform pivot queries it cold be very helpful to have the columns in a specific order. Also, sometimes I have been forced to write a bit more complex code which enumerates the columns by their names, or by part of the names used as a convention.
    You are right, in most cases it is a lot of overhead to use SSMS for this, but sometimes it is useful to have the columns in order.

  8. Although I agree that the insertion of columns in specific places is largely unnecessary, I can see the benefit of placing a new column next to old columns which may be loosely related to it. This is purely for the convenience of developers who are new to the database who may look at the table and expect similarly themed columns to be in the same place.

  9. We had a similar issue with our client, after adding so many columns after the design phase, they wanted to re-organize the columns in groups. My argument was that we should use the print out of the physical data model which has organized columns (produced in ERWIN for instance) to look into the table structure and not use SSMS to look into tables with 10s of columns…. But as we were still in development, we managed to generate the master DDL, manually reorganize the columns, drop the database and recreate it. Some new columns were added since, but they are few…

  10. inserting between two columns in sql server 2000
    go to enterprise select database and design table right click and insert column where u required
    1.select enterprises manager
    2.select database and Table design
    3.insert column were you required

  11. Here’s how to give the appearance of creating a new column ([NewCol]) in between two existing columns ([ColumnA], [ColumnB]).

    Use a view.

    ALTER TABLE MyTable
    ADD COLUMN [NewCol] [datatype];
    GO

    CREATE VIEW [vMyTable]
    SELECT [ColumnA], [NewCol], [ColumnB] FROM [MyTable];
    GO

    This will satisfy the unreasonable demands that a column appear between two other columns

  12. I think both arguments are valid:
    Having the fields of a table in certain order and that you should not care where a new column is added.
    And that is where most (all?) databases fail: It should not matter where the database engine inserts the column but it should be possible to define the field order independent of when the fields where added.
    The syntax
    ALTER TABLE table
    ADD new_column type AFTER existing_column
    that MySql offers does not imply that the database engine has to physically reorganize all the existing rows (as MySql also does). It can simply result in the metadata having the column added at the desired position.

  13. Why not use the comments on each column to store the column number? Then even a * type select could start with a Metadate “get columns” request, sort them by number order then create dynamique SQL. The cost of string operators for a dynmique SQL is relatively low, even if there are more than 100 columns

    • Comments? Do you mean extended properties?

      I’ve always heard that the shortcut is always the longest way to get there. Want proof? Let’s take KDoods suggestion.

      Let’s assume we start out with a table:

      CREATE TABLE [dbo].[MyTable]
      (
      [column_1] [int],
      [column_3] [int]
      )

      OOPS. I need to add [column_2]

      Let’s take KDoods suggestion: use extended properties to muck with column order.

      IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = ‘dbo’ AND [tbl].[name] = ‘MyTable’) DROP TABLE [dbo].[MyTable]
      GO

      CREATE TABLE [dbo].[MyTable]
      (
      [column_1] [int],
      [column_3] [int]
      )
      GO

      EXEC [sys].[sp_addextendedproperty]
      @name=N’column_order’,
      @value=N’1′,
      @level0type=N’SCHEMA’,
      @level0name=N’dbo’,
      @level1type=N’TABLE’,
      @level1name=N’MyTable’,
      @level2type=N’COLUMN’,
      @level2name=N’column_1′
      GO

      EXEC [sys].[sp_addextendedproperty]
      @name=N’column_order’,
      @value=N’2′,
      @level0type=N’SCHEMA’,
      @level0name=N’dbo’,
      @level1type=N’TABLE’,
      @level1name=N’MyTable’,
      @level2type=N’COLUMN’,
      @level2name=N’column_3′
      GO

      ALTER TABLE [dbo].[MyTable]
      ADD [column_2] [int]
      GO

      EXEC [sys].[sp_updateextendedproperty] – move column_3 to be column 3
      @name=N’column_order’,
      @value=N’3′,
      @level0type=N’SCHEMA’,
      @level0name=N’dbo’,
      @level1type=N’TABLE’,
      @level1name=N’MyTable’,
      @level2type=N’COLUMN’,
      @level2name=N’column_3′
      GO

      EXEC [sys].[sp_addextendedproperty] – column_2 to be column 2
      @name=N’column_order’,
      @value=N’2′,
      @level0type=N’SCHEMA’,
      @level0name=N’dbo’,
      @level1type=N’TABLE’,
      @level1name=N’MyTable’,
      @level2type=N’COLUMN’,
      @level2name=N’column_2′
      GO

      Of course, this doesn’t allow for dynamic re-ordering, doesn’t detect duplicate entries (two columns with the same ‘column_order’ value), or contiguous-ness.

      Now, how do we get the column order?

      SELECT
      [col].[name],
      [exp].[value]
      FROM
      [sys].[extended_properties] [exp]
      INNER JOIN [sys].[tables] [tbl] ON [tbl].[object_id] = [exp].[major_id]
      INNER JOIN [sys].[columns] [col] ON [col].[object_id] = [exp].[major_id] AND
      [col].[column_id] = [exp].[minor_id]
      INNER JOIN [sys].[schemas] [sch] ON [sch].[schema_id] = [tbl].[schema_id]
      WHERE
      [sch].[name] = ‘dbo’ AND
      [tbl].[name] = ‘MyTable’ AND
      [exp].[name] = ‘column_order’
      ORDER BY
      [exp].[value]

      And now we have everything we need to write dynamic SQL, if we only want to query one table, with no joins and no ordering

      SELECT
      ‘SELECT * FROM ‘ +
      REPLACE(REPLACE(REPLACE([data].[columns], ”, ‘,’), ”, ”), ”, ”)
      FROM
      (
      SELECT
      QUOTENAME([col].[name]) as [columns]
      FROM
      [sys].[extended_properties] [exp]
      INNER JOIN [sys].[tables] [tbl] ON [tbl].[object_id] = [exp].[major_id]
      INNER JOIN [sys].[columns] [col] ON [col].[object_id] = [exp].[major_id] AND
      [col].[column_id] = [exp].[minor_id]
      INNER JOIN [sys].[schemas] [sch] ON [sch].[schema_id] = [tbl].[schema_id]
      WHERE
      [sch].[name] = @schema_name AND
      [tbl].[name] = @table_name AND
      [exp].[name] = ‘column_order’
      ORDER BY
      [exp].[value]
      FOR XML PATH (”)
      ) [data] ([columns])

      • Wow you are so smart! What about just select from the original table into a new table with the new column in the correct place. then script the index of the original table and apply to the new one. When done delete the old table and rename the new one. Much easier.

        Other option:
        Use the Import wizard, select the table and destination table (table_new), edit mappings and add the new column to the script. Run this and script the old table index again and apply to new one. drop old table and rename new.

        Then you can populate your new column as you like.

  14. Not so complex like that, I think….
    – make an editable
    SELECT *
    INTO [dbo].[oldtbl_forEdit]
    FROM [dbo].[oldtbl];
    – add a column to tbl for edit
    ALTER TABLE [dbo].[oldtbl_forEdit] ADD
    [between_a_and_b] [varchar](200) NOT NULL DEFAULT ”;
    – resort the columns
    SELECT
    [a],[between_a_and_b], [b] …
    INTO [dbo].[SeizoShijiShosai_EditOK]
    FROM [dbo].[oldtbl_forEdit];

    From now on, finally what to do is just drop old or useless tables.
    IF I AM WRONG, pls tell me.

  15. hello Pinal,
    I am doing a bulk insert and I want the table structure to be altered according to the file. And it varies everytime, So I have to alter the columns (Mostly add it to the existing column). So if I have to do that I should be adding that at specific places.

    is it possible to add one column as the first column?
    Sreekanth

  16. Hello Pinal sir,
    Two user uses same databases.but One user wants to add new field.not necessary in other user.How will manage it. Is it have any solution. Suppose if i added new column in existing table, other user cant use my Application.

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

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

  19. 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: http://connect.microsoft.com/SQLServer/feedback/details/739788/alter-table-syntax-for-changing-column-order
    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s