Question: How to Add Column at Specific Location in Table?
Answer: Order of the column in the table should not matter.
Need of Column in Specific Location in a Table
I personally see no reason to create a column in a specific location in the table. However, I see following three popular arguments when I talk about that column order does not matter.
Argument #1
When I write my query with SELECT * FROM TABLE, it displays the columns in the order as they were originally created in the table. Some user does not like this option.
Solution #1
Instead of writing SELECT * FROM TABLE, spell out your column name SELECT column1, column2 FROM TABLE and now it should solve your problem. Anyway, using SELECT * is a bad idea. Here is the How to Add Column at Specific Location in Table where describes this in detail.
Argument #2
I like to keep my table tidy and I want my columns near to each other according to relevance.
Solution #2
In the database world, the data matters and not the aesthetic of the data. I think just like we can’t fix the entire internet, the same way we can’t fix every single column placement aesthetically. Just let it go. Add the column where ever you can and use the solution #1.
Argument #3
I have heard that if I select columns which are next to each other SQL Server gives me optimal performance, hence I want to keep two of the frequently used columns next to each other.
Solution #3
I think someone honestly made this one up. This argument is like, “If we know how to swim, we can drink water.“
A smart person can easily figure out that there is no relation between two parts of the previous statement. Though there are elements of truth, they are not related. We can always learn how to swim and we can always drink water. However, technically you do not have to know swimming to drink water or in the reverse order.
Performance tuning is a deep subject. Keeping column together is not the task you should be focused on for performance. A good index is sometimes 100s time more preferred over two columns together.
Additional Reading
You must read this article How to Add Column at Specific Location in Table which I have written earlier on this subject. In this article I discuss about good and bad approaches together.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I am not satisfied of your solution 2.
Yes, you are right, but sometimes you want a select * to return something beautiful, with all the things where you expect them. Yes, this is irrational :-)
But when you have some serious downtime available, you can still copy all data to a new table, delete the old one and rename the new to replace the old. Of course, FK must also be taken care of.
I understand and appreciate the answers and commentary to the question presented in this article (and mostly agree). But I think the real answer should be: “You can’t, unless you recreate the table with the new column and migrate the data.” Followed by the commentary above and additional points on why recreating the table is, generally speaking, probably a bad practice.
Love the blog!
I get what you are saying in THEORY, but in PRACTICE I dIsagree. When doing interactive data analysis on tables with a large number of columns, I find that SELECT * is useful when you may not know which column has the data you are looking for. Keeping columns logically grouped/ordered is helpful. If you want to see an example of a mess caused by the inability to place columns in logical order, look at MS Dynamics CRM tables.
Ideally, SQL Server would have a clause in the ALTER TABLE statement like Informix SQL has (I used Informix in past life).
It was basically of the format: ALTER TABLE tablename ADD newcolumn TYPE BEFORE existingcolumn
I agree with Mark’s comment regarding the correct answer to this question.
I also think the theoretical reasons for SQL behaving this way should have been pointed out. Namely, SQLs origins in Relational Database Theory.
A table in SQL is a set, with every row being an element of that set.
Each element has attributes that apply to that specific element.
The attributes in SQL are columns.
When you want a specific element, for example Customer, it’s meaningful to talk about the Name, or Address.
It’s not meaningful to say give me all Customer’s where [Attribute 9] contains Texas.
That’s not a RDBMS, that’s a list.
It goes to the heart of SQL that the columns of an row have no order.
See first paragraph https://en.wikipedia.org/wiki/Relation_(database)
I’m firmly in the camp of never relying on the ordinal position of the columns for anything. But playing devil’s advocate, you could also create a view which can be altered at any time (thus also altering the ordinal of each returned column.
Totally disagree. What about using nice way to copy table structure using {select top 0 * into [#mytbl] from [mytable]}? What about replacing totally hardcoded unpivot structure to transform columns to rows using xml and *? You must hardcode all columns, right? So, you need rewrite your code after table columns updated? No. Just use xml with * instead of hardcody unpivots. What about programmability comfort, when you have large set of colums, but, theyre related logically? Code readability does not matter? What about adding column after rowversion column, and before it? Hudge, hudge difference!
So, my opinion is: wrong answer, no job. Right answer is: you need recreate table, if adding column to not last position. And please comfortably, but with arguments use *. And using normal forms to infinity is waste of time and code readability. Need to keep smart balance here.