SQL Server Column Display Order

Question

When you create a table via scripts it appears to just list the columns in order of creation. It's a minor annoyance, but I like a certain method to the madness. When I later add a column with ALTER TABLE, how do I make it show up in a specific place when viewing the table in interactive tools such as enterprise manager?

ex:

Table Foo
---------
FooID  
BarID  
Name  
Address  
Worth

I want to

ALTER TABLE Foo  
ADD BazID INT NULL

and have BazID listed between BarID and Name when I use Management Studio.

Answer 1

You can't do that - a SQL Server table really doesn't know anything about order of columns - there is no order in the tuple algebra that's the foundation of the relational database systems.

What SQL Server Management Studio shows you is just its own display "optimization" - you can change it there, but the standard SQL Data Definition Language (DDL) statement have no notion of "column order" - you cannot ALTER a table to move a column to a different position.

If you change the column display order in SSMS, what it'll do in the background is rename the old table to a temporary name, create the new table with the columns in the order you specified, and then copy around the data. That's why modifying the column order on a large table can take almost forever - it's not just a simple little DDL statement to be executed - it's a major undertaking.

Marc

Answer 2

The way the sql server management studio and other big name tools like redgate do it, is to make a new temp table, copy the information over, then drop the old table (constraints & unique indexes first), rename the temp table to the old table name, then re-add any constraints and indexes. You really can't re-order what's already there.

Here's an example:

-- here's a sales table
CREATE TABLE [dbo].[Sales](
    [SalesId] [int] IDENTITY(1,1) NOT NULL,
    [Month] [int] NOT NULL,
    [Year] [int] NOT NULL,
    [InvoiceAmount] [decimal](15, 2) NOT NULL,
    [SalesRepId] [int] NOT NULL,
    [BranchId] [int] NOT NULL,
 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
    [SalesId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


--Here's the sales table adding a column called description between SalesId & Month

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Sales
    (
    SalesId int NOT NULL IDENTITY (1, 1),
    Description varchar(MAX) NULL,
    Month int NOT NULL,
    Year int NOT NULL,
    InvoiceAmount decimal(15, 2) NOT NULL,
    SalesRepId int NOT NULL,
    BranchId int NOT NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Sales ON
GO
IF EXISTS(SELECT * FROM dbo.Sales)
     EXEC('INSERT INTO dbo.Tmp_Sales (SalesId, Month, Year, InvoiceAmount, SalesRepId, BranchId)
     SELECT SalesId, Month, Year, InvoiceAmount, SalesRepId, BranchId FROM dbo.Sales WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Sales OFF
GO
DROP TABLE dbo.Sales
GO
EXECUTE sp_rename N'dbo.Tmp_Sales', N'Sales', 'OBJECT' 
GO
ALTER TABLE dbo.Sales ADD CONSTRAINT
    PK_Sales PRIMARY KEY CLUSTERED 
    (
    SalesId
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT