July 2, 2015 at 10:02 am
Hi,
When I add three columns to a table, they always go to the end of it.
I want to add a column as the third of the table, the second column that I want to add needs to be at the fifth place.
How can I do this using t-sql?
Is this possible?
July 2, 2015 at 10:11 am
river1 (7/2/2015)
Hi,When I add three columns to a table, they always go to the end of it.
I want to add a column as the third of the table, the second column that I want to add needs to be at the fifth place.
How can I do this using t-sql?
Is this possible?
As far as I know, it's not possible without recreating the table.
The only way I know to do it involves renaming the table and creating a new table with the new and old columns in the order you want and copying data from the old table to the new, then renaming the new table to the old name.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 2, 2015 at 10:28 am
I agree with Alvin. The question is why do you care what order the columns are stored in? Your queries should be explicitly naming the columns anyway. Just put the columns in the order you want in your select statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2015 at 10:30 am
Sean Lange (7/2/2015)
I agree with Alvin. The question is why do you care what order the columns are stored in? Your queries should be explicitly naming the columns anyway. Just put the columns in the order you want in your select statement.
Agree!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 2, 2015 at 3:01 pm
river1 (7/2/2015)
I want to add a column as the third of the table, the second column that I want to add needs to be at the fifth place.
Why?
The order of columns in a table is irrelevant (unless you use SELECT *, which you shouldn't be using in production code). It doesn't define the physical storage order, it doesn't affect performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2015 at 2:19 am
because of what gail told (select *)
July 3, 2015 at 3:05 am
Well you shouldn't be using SELECT * in production code. Fix your selects to select just the columns they need in the order they need and then you won't have to recreate tables to add columns (because that's the only way to add columns anywhere other than at the end)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2015 at 3:13 am
ok. thank you all for the feedback.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply