March 31, 2008 at 2:29 am
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.
March 31, 2008 at 2:44 am
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.
March 31, 2008 at 4:49 am
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?
March 31, 2008 at 4:58 am
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.
March 31, 2008 at 7:27 am
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
March 31, 2008 at 7:36 am
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.
March 31, 2008 at 7:41 am
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.
March 31, 2008 at 7:44 am
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
October 25, 2008 at 10:17 am
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.
October 25, 2008 at 11:05 am
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
Change is inevitable... Change for the better is not.
October 25, 2008 at 11:53 am
WILLIAM MITCHELL (10/25/2008)
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.
Yes, this is very easy to do with SSMS and this is how you do it with SSMS. However, despite appearances, that is not what SSMS actually does. SSMS actually does exactly what SQLZ said: it creates a new table, copies the data from the old table to the new table, drops the old table and then renames the new table. And it takes just as long for the server to do it as it would it you typed it in yourself (except for the typing, of course).
baumgaertner: And yes, it can take a looong time. And although this might be OK for development, I would strongly discourage you from ever doing this in production as re-ordering columns could break all kinds of things that you never thought about. In fact, IMO, it is a best practice to always only add new columns to the end of the column list for just this reason.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 25, 2008 at 1:23 pm
rbarryyoung (10/25/2008)
Yes, this is very easy to do with SSMS and this is how you do it with SSMS. However, despite appearances, that is not what SSMS actually does. SSMS actually does exactly what SQLZ said: it creates a new table, copies the data from the old table to the new table, drops the old table and then renames the new table. And it takes just as long for the server to do it as it would it you typed it in yourself (except for the typing, of course).
You can see what SSMS does behind the scenes like this:
Make a change to your table in design view - don't save it - then click Table Designer ~ Generate Change Script and you can save your change as a script. And then close the design view without saving and then run the script to apply the change.
So if you ignore everybody's advice and insist that you have to move columns, at least there is an easy way to do it using a script. That way, as you migrate the change from dev to test to production it will be documented.
October 27, 2008 at 2:31 am
In this case it is not possible to use SSMS.
The requirement is to use a script which must be distributed.
I need a standard approch for "Change Ordinal Column Position" that works on every table independant from PK's, FK's.
October 27, 2008 at 7:49 am
You should just create views for this purpose(if you really need to create anything at all), as previously mentioned in this thread. What you're basically asking for with this change is a script to promote laziness and inefficient coding practices that can cause all sorts of issues down the line.
October 27, 2008 at 8:55 am
baumgaertner: It has already been explained how to do this with a script, even how to use SSMS to generate this script for you. Please let us know if this is not sufficient.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply