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
I’m surprised we have all these professionals talking about views as if they are just a workaround for ordering columns or something to be avoided because of the maintenance overhead. Views should be an integral part of any database design. Almost all user access to the data should be controlled and Views can be an essential part of that control. You don’t give users uncontrolled access to the database, nor do you give them full access to individual tables. You give them a subset of data only accessible via views. If you have this as part your database security design, the order of the columns in the underlying data tables is irrelevant. If one group needs an additional column, you add it to the table and to their particular view without compromising the access enjoyed by the other users. None of the BI needs to be changed except for the group that requires the new column.
So please don’t look at views as a workaround or a column ordering add-on or something to be used occasionally because the data is a mess, see them as a serious tool for secure access to the data and make them part of the database design.
One last word with regard to bulk insert. My rule of thumb is to always bulk insert into a view. You don’t have to use temporary tables and it is easy maintenance if the source data format changes.
If column order is so irrelevant, then it is equally irrelevant for the logical ordering (that is displayed to the user) to be physically coupled to the physical ordering of the storage heap. The data dictionary just so happens to tell us the physical order, but there is no hard requirement for that to be so, and nowadays it is mostly a relic of when storage order actually mattered. While I agree that good SQL practices will make you immune to column order, arguing that its simple enough to fix in a view, or in a query, is arguing that SQL Server itself just needs to make the ordering configurable. A simple field added to the data dictionary to allow logical order to be specified differently than physical order would allow tools like SSMS, ER tools, and schema compare tools to ignore the physical ordering. You can’t dismiss ordering requirements as “unreasonable” without making the case that physical ordering never matters in the first place, so why establish any guarantees around it? The answer is, because sometimes it _does_ matter. Either way, a better architecture of the RDBMS engine / data dictionary would decouple the physical from logical and let it be dynamically configured _without_ the supplementary crutch of a view.
Well said.
Use Design! You can drag your column anywhere
What’s that?
I believe Thka is talking about SSMS, rightclick on table, choose Design. Then you can drag and drop a column to anywhere you want. Of course, in the background, it is performing a CREATE, INSERT, DROP, RENAME which is fine for a table with few or no rows. With a table with millions of rows as we have been talking about, SSMS will timeout.
Thanks for the clarification KISS. You are correct. Once you re-deign the table, generate a script and that can help you in avoiding timeout.
I have tables that are used to create flat files. I have the columns designed so that they are in the correct order and I can just concatenate them together. Now I need to add columns to the middle of those files. I get that I can do Select Col1 + Col4 + Col2 if I add them to the end of the table, but it just doesn’t look right if I’m doing something like Select top 1000 from SSMS or I’m looking at the table structure in object explorer.
Honestly, that will recreate the table as I mentioned in the blog post and not a great idea.
What about Sql Server Data Tools, we always work with the create definition of the table, does this tool update the database in the same way the ssms?
I have a need to add the column at specific location on the table because this table get new data from a export flat file. Currently the table has about 6 millions records and It gets about 100K new rows very 2 weeks. What is my best option? I would hate to go through to recreate table route that you mentioned but not recommended in your post.
Hi, what is the solution to add new column in specific position in sql server,
we have table with 5 default columns , later using web application we add new columns(run time), so it will adding after 5,6,7 and so on.
But now we change table structure according to customer needs, so we have to decide add 2 more column as default columns when first time creation of table. For newly create table no problem, but for exiting tables in customer place, we need to use one tool to add 2 columns in the position of 6 and 7, I know column position in not matter when retentive time we arrange it, but in our web application we skip 5 default columns and take others, so if table got 5 default and 4 run time added column, in this if add 2 more columns, it will adding in at last, so if skip 7 columns, then it will wrong.
For this we need to add columns at specific 6 and 7 location.
Note: by default we create 5 columns when create table.
Aravind
its a bad idea to think that if somebody needs to add a column at a given position then they dont know that they can use a specific column order when creating a sql query. In real word sometimes you actually need a column to be on that specific position because of some old programs that they use select * and you dont have access to the program code, is nothing you can do. So, as a general principle, offer a solution to the problem instead of saying this is a false problem, you need to do something else.
Hi Ionut,
To be super honest, if you have an application which is dependent on column order, I believe you will have to recreate the table in the order you want columns, you can’t add a column in middle in SQL Server unless you create and drop table.
I hope this helps.
I haven’t had time to read all of the comments here but my feeling is that this article doesn’t actually address the problem at all. Sure, I can retrieve the columns in whatever order I want without caring what order they’re in the table, but as a developer consistency is my friend, especially when tables can have dozens of rows. I’ve worked with databases where most tables have common columns (e.g. ‘Deleted’) but those columns are in different positions in every table and it’s very frustrating. I much prefer to locate certain common columns in the same position in every table, e.g. Primary Key column first, ‘Deleted’ penultimate and ‘LastModifiedBy’ last. The specific order isn’t important, but the fact that I always know where to find these columns in a table makes my life easier. As a result, if I’m adding a new column to a table I need to know how to add it somewhere other than at the end. I just achieve this by dragging it in SSMS (or using SQL Schema Compare in Visual Studio).
IS IT POSSIBLE ? AFTER CREATED TABLE , ADD THE ONE MORE ROW IN TABLE.
Just use INSERT statement to add new row
I’m always amazed that authorities insist there is one best practice. The best practice depends on the application as in the reasons for which something is applied. I don’t think that I read that a logical order of some kind might be there for the mere reason of easily finding it.
Views can make it easier to implement a security model. And if they are kept very simple as to only reference a single table can be effective. Even so, columns that have simple calculations can create performance issues. If Views are used to reference multiple tables, a user (another programmer) can further degrade performance by placing JOINs on two differing views that reference the same underlying table object, and even possibly cause unnecessary nesting. Naturally that further complicates diagnostics if there is a data issue. I see these issues often. I swore after the problems I’ve incurred that I would never use views again. My last data store only had two views where there were as many as a few hundred tables. Security was managed in other ways and perhaps not as complex as others that I’ve seen.
Bottom line is that every environment is different and every plan doesn’t have to be the same as to say that there is only one right way to do something. Master the fundamentals first. Apply and practice them to experience how they do or don’t work. Break the rule to make a best practice for the overall situation.
My apologies for another long narrative. But perhaps I would just like to see the answers simplified to a specific question. The best answer was that SQL Server doesn’t support it. Maybe there are physical/logical reasons, but maybe it doesn’t matter and Microsoft should just provide it.
Just my beef for the day..lol!
Here is the solution. Use ALTER table statement adding column(s) to the end. THEN use SSMS design mode for table to move column(s) to desired position(s) in the table. After you click “Save” it will not recreate table but change order of columns internally. So no timeout even on huge tables. You are welcome.
That actually drops and recreates the table behind the scene and very expensive operation.
That’s not good unfortunately, why is that Microsoft did not give this feature through scripts?
I couldnt belive that, it is really sad that you can do that in MySQL but in a Microsoft tool you cant, and worse you have to explain why you should get used to not having that feature and be happy to pay for it in production environments while in MySQL you dont have to.