SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL-Server 2005: Change Ordinal Column Position


SQL-Server 2005: Change Ordinal Column Position

Author
Message
baumgaertner
baumgaertner
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 129
Hello,

does anyone know, if it is possible to change the ordinal position of a column?

I tried the following:
update syscolumns set colorder=2 where name='strName' and id=object_id('tblTest')
update syscolumns set colorder=1 where name='strValue' and id=object_id('tblTest')
-> SQL-Server does not allow it


Thanks.
SQLZ
SQLZ
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4754 Visits: 940
As far as I'm aware the only way to achieve this is to drop and re-create the table. If the table has data then you'll need to create the new table first, with a different name, copy the data across to the new table, drop the old table and then rename the new table to match the name of the old table. Look at the change scripts that EM creates when you change the ordinal position through the designer.

Karl
source control for SQL Server
baumgaertner
baumgaertner
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 129
Thank you for you answer, Karl.
But I do not want to rename the table and then insert the data to the new table.
When you have big tables this approach is slow.
Are there no alternatives?
SQLZ
SQLZ
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4754 Visits: 940
baumgaertner (3/31/2008)
Thank you for you answer, Karl.
But I do not want to rename the table and then insert the data to the new table.
When you have big tables this approach is slow.
Are there no alternatives?


Unfortunately I don't think there's any other way. Is there any reason why you want to change the column position? It shouldn't really matter what position it's in.

Karl
source control for SQL Server
baumgaertner
baumgaertner
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 129
Hello,

the reason for changing the column position is a design question.
I think the columns of a table should be logical sorted.

Regards Daniel
Steve Jones
Steve Jones
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: Administrators
Points: 223861 Visits: 19630
There is no reason to design the columns in any order. As far as I know, it's not a best practice to make them alphabetical, or any other order.

There's no way to move the order of the columns, and according to basic database theory, the order of the columns, just like the order of the rows, does not matter. SELECT allows you to return them in any order without a performance penalty.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
baumgaertner
baumgaertner
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 129
Yes, that's all right, but if you make a "SELECT * FROM tblXXX" on a table with 100 columns the columns should be logical sorted so that you find the information you need.
Cory E.
Cory E.
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2745 Visits: 10845
An option - you could make a view that has the columns defined in the order you want.

Also, I think it has been fairly well discussed that "Select * from tbl" is not the best way to do things.

-- Cory
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5492 Visits: 3116
I'm not in favor of changing the order of columns, but it is possible.

It is very easy to change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse.

I can think of one scenario where you might want to change the order of the columns - one best practice that I've seen is to have columns for the record creation date & user as the last two columns. If you need to add a new column to the table, it would be nice to keep those two columns at the end.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339258 Visits: 42622
baumgaertner (3/31/2008)
Thank you for you answer, Karl.
But I do not want to rename the table and then insert the data to the new table.
When you have big tables this approach is slow.
Are there no alternatives?


How many rows? How many indexes? What is the PK? Any FK's?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search