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.
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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 withDownside, 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!! |
|
|
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2015-04-29 : 10:24:56
|
| thanks! |
|
|
|
|
|
|
|
|
t-sql 2012 add columns to a table