Changing column order in Table
-
Hi,
Is there a way to change the order of columns/column ordinance in the table?
any suggestions/inputs would help
Thanks
-
Why would you want to do this? Normally, the column order in a relational database should have no meaning. You select columns in the order you want to see them. If you're trying to make sure that a "SELECT *" returrns a predictably ordered column set, my advice would be to explicitly name the columns in the select.
And then again, I might be wrong ...
David Webb -
you can do this through EM, I'd just script and recreate the table though, don't see why you're asking the question.
Like David I'm interested as to why?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I can't speak for the original poster, but I find it much eaiser to locate a column of interest when presented with a list of column names in alphabetical order. In access, each column had a ordinal property that made it easy to force a presentation order. I haven't found that yet in SQL Server, but want to.
-
it's called a select list. Ordinal positions are one of the worst practices along with select *.
If you need to structure your table it's not that difficult - data transformations do exactly that and can easily be coded. Generally the physical location of columns is not a consideration in a relational system.
Your documentation / data dictionary can be arranged to list your columns in order, again you can use a query to do this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
It works the same way as in Access. In Enterprise Manager, right click on the table and choose Design Table. Highlight the column, drag and drop. I also appreciate being able to do this as it saves time.
-
HOW does it save time?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Based on basic relational theory, there's no guarantee that 'select *' will always bring columns back in the order defined. The ordering of columns in a relational table should have no meaning, not even positional meaning. MS happens to deliver the data in definition order now, but there's no rule that such an ordering has to be preserved across versions of the product. One of the foundational ideas of the relational model was that data could be addressed by name, not position, and this carries over into basic display.
And then again, I might be wrong ...
David Webb -
absolutely! And I'd ban Access < grin > , no offence to the original poster, but I sometimes think that this style of thinking is the basis for so many poor applications that the DBA then has to support, and it seems the more you pay for your app the worse it becomes!!
I worked one company where all developers who worked with sql server had to attend the ms sql training courses before being allowed to start any development ( to make sure they had an understanding of how sql server worked, set theory, RFI etc. etc. ) and I worked one where they never considered knowing sql to be a requisite for developers - guess who had the best apps and databases ? ( Sorry - bad start to day - just having a small rant! )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I don't disagree with anything you say, except for your comment about select * which was not part of the original discussion.
I'm talking about the order in which columns are listed in the management studio, or the order in which they are listed in the query editor when you "script table as/select".
Worse, SQL Server seems to think that the order is important -- I tried the earlier suggestion of "Design Table" (it is "Modify" when I right click a table name?) I dragged a column to another position. (I really want to be able to script it, but just to test...) SQL Server warned me it might take a long time, and then timed out. I can't believe that it was really trying to move the column physically!
As to the anti-Access bias shown in other posts -- I chalk it up to group think! Have any of you expressing that position had any serious experience with the Application?
-
As mentioned previously, Sql Server returns the columns in the physical order they were defined, so the only way to change their order is to redefine them. This is done by creating a new temporary table, copying all the rows from the original to the temp, dropping the original, and renaming the temp.
If you check the options along the top of the editor, one of them should allow you to save the script being used to do this so you can tweek it, etc.
As for management studio, I really wish it would just list columns first by primary key, then by name.
-
when you use EM to order columns inside a table, it creates a new table with desired structure, moves your data to that table, drops the old one, and rename the newly table table to orginal table.
Be careful before you use it.
BTW, the order of columns defined in create table statement has no impact on the way the data is stored inside database.
-
BTW, the order of columns defined in create table statement has no impact on the way the data is stored inside database. You got some example code of where it doesn't?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Code wouldn't show it. My rememberance of this may be rusty so more knowledgeable folks should please jump in if I mis-state this, but internally, for each row, the fixed width columns would be stored first, follwed by the variable width columns. I don't remember where the text pointers are stored, but probably with the fixed width columns. The server re-orders this stuff to display it anyway.
As I recall, Ken Henderson's book had a really good description of row layouts and how efficiencies were gained for the query engine by column placement. None of this is exposed to the user, so the realtional tenents are preserved.
And then again, I might be wrong ...
David Webb -
Ah... got it, David... thought you were talking about something else.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply