Reording column storage order
-
Is there a way of using a sql script to reorder the storage of columns in a table? I know I can move columns by editing the table and then using click and drag in SQL Server Management Studio … but I want a sql script. Is there a system table I can update?
-
Just one comment/thought, be careful if you start futzing with the order of columns in a table if you're not absolutely certain that your client side code is not using ordinals... folks have been known to use that particular trick in combination with a "select * from"...
One alternative to changing the tables themselves would be to create a view that presents the columns of the table in the order you would like to see them.
Joe
-
Well, the short answer is: create table, insert into.
This is exactly what SQL Server is doing when you use the method you mention. SQL Server will most likely waste space. Important: Create a new table, and avoid adding and dropping columns, and then loading the data from somewhere else (like file). The best choice is to rebuild the table as above.
<For suicidal people>
In case you want to learn a lot about the internal table structures, and are happy to sacrifice a few databases, then here are a few hints for tweaking with the system tables: it may be possible. On 2000 you would need to fluff around with syscolumns, on 2005, since most system tables are hidden, you would need admin connection, then play with sys.syshobtcolumns.
</For suicidal people>
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
just my 2ct
- with SSMS you can script practicaly every action you perform.
So, Design your table and push the "script changes" button.
(and the ofr corse cancel if you don't want to modify at that time)
- Column order is rather a logical issue. (i.e. to be able to read the columns in their "human" logical order is nice to have)
For sql I would say the guidelines are to put the fixed width columns at the beginning of a row, then the "off row" pointers (varchar(max),...) , then the (in row) variable ones.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Alternatively, just do not care about column order at all. Access them by column name. And forget "select *"
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
"select *
" indeed is one of the worse practises ! 
but so commonly used
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanks everyone for your input.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply