Hello, Ask Tom.
I was asked to change the order of columns in a table. How can get this done without user interruption?
BTW, what do I gain ordering columns? Some space because null columns are placed at the end of the table? Performance?
Thanks in advanced.
Unless the table has more than 255 columns, you get minimal gains from moving the columns around. It is *slightly* faster to do:
select [first_few_cols]
from table
where first_col =
than
select [last_few_cols]
from table
where later_col =
but we're talking fractions of a percent here.
In terms of space, once we hit the last non-null column, we don't need space for the any trailing nulls, so for example in a 10 column table:
A,null,B,null,C,null,D,null,E,null
uses a little more space then
A,B,C,D,E,null,null,null,null,null
but seriously... I would not be worrying about it. There are normally much bigger opportunities for performance improvement in terms of design, indexing and good SQL code