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)
93 Comments. Leave new
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 .
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.
I know this is old, but I just ended up here…
If you are trying to keep things simple for a human looking for the data, obfuscate the actual table access through a view and then just manage the view as you see fit.
The way we structure the data and the way we logically present it are abstracted allowing greater flexibility for either side.
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.
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 :)
Before saving table, check “Generate Change Script” option from Table Designer Menu or toolbar, which will show you entire script for recreating table as mentioned in post.
ADD column in table design(SSMS) and drag it to the desired position :-)
Bad Idea but sometimes helpful
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.
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.
Is there something like this
Alter table add column @index1
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.
instead of * we can use Select Col1, Col2,Col3 From tab
Good One…
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.
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…
//SQL SERVER for adding new columns in the table
ALTER TABLE TABLENAME
ADD COLUMNNAME1 DATATYPE, COULUMNNAME2 DATATYPE
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
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
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.
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.
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.
A lot of the comments here are expecting small tables (less than 500Million rows, no partitioning, no compression).
If you are working with data of any appreciable size, then this is going to take you forever to build the table, and then even longer to index/compress afterwards.
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
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.