I can change the ordinal position of a column in a table by changing the value for colid on columns in the syscolumns tables. But I get an error message when I then try to create an index using the column whose position I've changed. Is there a way to change the ordinal position of a column in a table without recreating the table?
Modifying data in the system tables is incredibly dangerous. There are a few, specific cases when you can change data in a system table without disastrous consequences, but this isn't one of them.
Unfortunately, there isn't a supported way to change the ordinal position of an existing column or add a new column to any position other than at the end of the table without recreating the table. I've seen many new DBAs waste a lot of time searching for this solution. Enterprise Manager appears to be able to change columns' ordinal positions because it lets you move columns in the UI, but Enterprise Manager simply recreates the table for you.
Why do you want to change the column order? It's a common misconception that column order affects performance. But keeping columns that are referenced in the same query near each other doesn't produce faster results. In fact, the visible order of the columns in the table doesn't reflect the physical order of the columns stored in the database. You won't see any performance gain by changing the order of the columns in your table. You will, however, see a performance difference by changing the order of columns in an index, but the difference might not be for the better.
