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)
