ansaurus

Question

Answer 1

+2 A:

You can do this easily enough in the designer (SSMS) - just drag the columns around to the correct order and then save - in the background it will script out and re-create the table.

But why do you need to do it? Order of columns has no impact other than making things look pretty in the designer.

Chris W 2009-07-20 09:12:07

Gary McGill 2009-07-20 09:14:37

Enforcing this on any kind of decent sized database is this issue - if someone starts re-ordering columns in a live DB using the designer doesn't appreciate what happens under the covers of SSMS then they get a big surprise when the table locks up as it's recreated.

Chris W 2009-07-20 09:19:04

Note Mar Gravell's comment on this issue where the table is replicated as another reason...

Chris W 2009-07-20 09:19:55

@Chris W: Good point well made! Re-ordering columns in SSMS drops and and recreates the table behind the scenes. If you're at the design stage of your database no problem but if it's a live table with millions of records you could be in for trouble.

John Sansom 2009-07-20 09:28:26

The best idea is don't do this - the order of columns should not be relied upon or changed; basic rule of defensive programming with SQL.

onupdatecascade 2009-07-22 16:19:17

Answer 2

+2 A:

Can you just use a view based on the table to get the output you want? That's my first reaction.

Galwegian 2009-07-20 09:12:19

Answer 3

+2 A:

Right-click on the table, choose "Design", then drag the columns up or down.

Gary McGill 2009-07-20 09:12:21

Answer 4

+4 A:

Moving columns involves a lot of DDL, and doesn't actually do anything.

If the table is currently empty, fine... but if it has lots of data, this can be very painful. And if the table is currently replicated: even worse.

IMO, you should just handle this in the SELECT statement:

SELECT colA1, colA2, colA3, colA4, colA5, colA6, colB1, colB2, colB3
FROM   TheTable

Marc Gravell 2009-07-20 09:16:01

ansaurus

tags:

views:

answers:

Rearrage column order in sql server

related questions