t-sql 2012 add new columns to a table

Question

t-sql 2012 add new columns to a table

wendy elizabeth

SSCoach

Points: 16915
More actions
April 28, 2015 at 11:13 am

#317037

I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.
However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?
Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ALTER statement always adds columns to the end. it does not support identifying the column_id and stuff like that.

SQL doesn't really care the order of the columns , but development wise, you might want to group columns together logically, but it's not required.

So SQL wise, column order does not matter, but work-wise, it often makes sense to have the columns in some sort of order that makes for an understandible design. it's not uncommon to tweak tables due to this.

if you want to insert columns into the middle, you actually have to drop and recreate the table;, which is what the SSMS Designer does, when you insert a column in the middle of the table:

a new table is created with a temporary name and the new structure, the data is moved, the exisitng constraints are dropped ,and the old table is dropped, and finally the new table is created, and the contrants put back on the new table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick thought, the order of the columns is irrelevant and cannot be set, neither by a create nor alter statement. The ordinal position is the representation and has nothing to do with the physical storage.

😎

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

Lowell is exactly right. I would just add the following:

You may need to be aware of what is expected by any applications that may access your table. Now, if you are in complete control of those applications, you can modify those as well and this is not an issue. Or, if your applications always access columns in returned data sets by column name, or through views that reorganize column into a particular order, it is also, probably, not an issue.

But... you may have the joy of maintaining databases where applications (or base object libraries) expect column to be in a particular order, probably referencing them by ordinal position in a returned data set (from 'Select *' type queries). In that case, you have to be sure that you will not be breaking any applications by inserting columns into the 'middle' of a list of columns or, for that matter, by adding them to the beginning or end. As a DBAdmin, I would advocate that such application developers should be keelhauled, tarred and feathered, and nailed to the wall. But, we all have to get along, so we make sure we are good citizens and ask those questions before jumping in and making changes.

Rob Schripsema
Propack, Inc.