Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL-Server 2005: Change Ordinal Column Position Expand / Collapse
Author
Message
Posted Monday, March 31, 2008 2:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 12, 2009 8:16 AM
Points: 38, 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.
Post #476826
Posted Monday, March 31, 2008 2:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:29 AM
Points: 870, Visits: 915
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
Post #476830
Posted Monday, March 31, 2008 4:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 12, 2009 8:16 AM
Points: 38, 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?
Post #476880
Posted Monday, March 31, 2008 4:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:29 AM
Points: 870, Visits: 915
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
Post #476881
Posted Monday, March 31, 2008 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 12, 2009 8:16 AM
Points: 38, 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
Post #476963
Posted Monday, March 31, 2008 7:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #476974
Posted Monday, March 31, 2008 7:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 12, 2009 8:16 AM
Points: 38, 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.
Post #476983
Posted Monday, March 31, 2008 7:44 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
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
Post #476986
Posted Saturday, October 25, 2008 10:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:23 AM
Points: 1,522, Visits: 2,731
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.
Post #591707
Posted Saturday, October 25, 2008 11:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #591712
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse