Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 t-sql 2012 add columns to a table

Author  Topic 

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-04-28 : 13:14:01
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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-28 : 13:17:32
Always add the columns to the end of the table. If you add them to the middle, the table has to be dropped and recreated. Where the columns exist in the table doesn't matter. Period. Add a view if it matters when developing in SSMS.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-28 : 17:04:30
We group columns together in the table where there is some sort of logically connection. Reason being that we have found that it reduces bugs during code maintenance, and reduces code development cost.

Developers may overlook "related" columns if they are elsewhere in the table, rather than adjacent to the other columns that they have some "logical" connection with

Downside, as Tara has pointed out, is that the table needs to be completely recreated, data copied across, foreign keys dropped, and recreated, and a raft of other such issues.

If you add the columns in SSMS (you can "insert" where you want then, in the table designer [I assume it is the same in SQL2012], and SSMS will then generate a script which will do all the steps above. On a large table it needs both time and disk space to do this, whereas adding a column at the end of the table is "instant" (in most cases)

If the SQL Development tools you use display all the columns in Alphabetic Order (as some do ) then it won't make any difference where you put them in the table because the only "Logical grouping" will be alphabetical!!
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-04-29 : 10:24:56
thanks!
Go to Top of Page
   

- Advertisement -