Specifying position when adding a column

Question

Specifying position when adding a column

oallain

SSC Veteran

Points: 291
More actions
August 28, 2002 at 2:05 pm

#79372

Hi guys, do you know a way to add a column specifying its position in the table? Any
T-SQL or SQL-DMO solution would be nice.
Olivier

Viewing 8 posts - 1 through 7 (of 7 total)

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

You can do it in EM. Other than that you can do it yourself by bcp'ing the data to a temp table, altering the table to be the way you want, bcp back in. Generally recommended not to worry about column order, you can always use a view to customize it.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

katravax Old Hand Points: 353 More actions · Answer 2

Alternately, create new table with columns like you want them, INSERT SELECT all the rows into the new table, drop the old table, sp_rename the new. That's essentially what Enterprise Manager does (run profiler on it when using it to see some neat tricks). But if you're looking for a simple way to say "put the new column between the existing second and third columns" in code, then there's not really a way to do that in one simple step.

- Troy King

Antares686 SSC Guru Points: 125444 More actions · Answer 3

The only problem with that last trick is you will lose constriant, indexes, permissions, etc. EM scripts out all that stuff for you in addition. However I agree on the one fact. The column position only matters when looking at the physical table or doing a SELECT *, which really is a personal preference. Other than that when you actually query you can order the column output in your query how you want.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Keep in mind that the tenets behind the "theoretical underpinnings" of relational database systems say the columns and rows can be in any order.... as a result, if specific order is needed, this is done on the retrieval.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

oallain SSC Veteran Points: 291 More actions · Answer 5

ok, guys, it's just that I needed to do add a column in the first position for like 100 tables, so I would have liked to be able to generate a script automatically or use SQL-DMO. My tables have a lot of columns and I'm trying to keep them in a logical order for easier maintenance. I know how EM does it in the background but it's pretty heavy, especially if I want to do it for 100 tables with a lot of constraints on it.

I'll do it manually with the good old EM.

Thanks.

Oliv'

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

Is there a reason the column order is important?