7

How To change the column order in sql server 2008 R2?

Eg: My table order is Eid------Ename-----Esalary

Know i want this format like Eid---Salary----Ename

Thanks.

1
  • Why don't you want to create a view that has the columns in the desired order? Jan 14, 2018 at 22:13

6 Answers 6

10

You shouldn't care enough about the physical column ordering of an existing table that you would want to invite the risks involved with changing it. The only case where this might be a concern is if you're using SELECT * and want that output arranged in a certain way - and you shouldn't be doing that, you should be listing the column names explicitly (and you can then put the columns in any order you want, or create a view that does that).

You can drop and re-create the table, like others have said, with the columns in your preferred order. But this is going to be very disruptive (blocking, I/O) if your table is large, since all of the other users on the system will have to wait for that to complete. If you want to see exactly what Management Studio does, create this table:

CREATE TABLE dbo.foo(b INT, a INT, r INT);

INSERT dbo.foo(b,a,r) VALUES(1,2,3),(4,5,6);

Now, in Object Explorer, right-click the table and choose Design. Select the box to the left of the column name that you want to reorder. Drag the column to another location within the table. I moved a above b because I'm OCD and wanted the columns in alphabetical order:

enter image description here

Then I clicked the "Generate Change Script" button, and here is the monstrosity that it created (and note this is a heap with no keys, constraints, triggers etc. - this re-create can get pretty complex).

/*
   To prevent any potential data loss issues, you should review 
   this script in detail before running it outside the context 
   of the database designer.
*/
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_foo
    (
    a int NULL,
    b int NULL,
    r int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_foo SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.foo)
     EXEC('INSERT INTO dbo.Tmp_foo (a, b, r)
        SELECT a, b, r FROM dbo.foo WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.foo
GO
EXECUTE sp_rename N'dbo.Tmp_foo', N'foo', 'OBJECT' 
GO
COMMIT

For fun, let's create something a little more complex:

CREATE TABLE dbo.flarb
(
  a INT PRIMARY KEY, 
  b INT NOT NULL CHECK (b>1), 
  c ROWVERSION, 
  d DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  e UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
);

CREATE TABLE dbo.blarf
(
  a INT NOT NULL FOREIGN KEY REFERENCES dbo.flarb(a)
);
GO

CREATE TRIGGER dbo.flarb_tr
ON dbo.flarb
FOR UPDATE
AS
  SELECT 1;
GO

CREATE VIEW dbo.flarb_v
WITH SCHEMABINDING
AS
  SELECT a FROM dbo.flarb;

Now we try the same thing:

'flarb' table
- Error validating the default for column 'e'.
- Warning: The following schema-bound objects will be modified:
- View 'dbo.flarb_v': schema binding will be removed.

Oops. Thankfully, we can still proceed to see what the script will look like:

/* 
  To prevent any potential data loss issues, you should review 
  this script in detail before running it outside the context 
  of the database designer.
*/
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
ALTER VIEW dbo.flarb_v

AS
  SELECT a FROM dbo.flarb;GO
ALTER TABLE dbo.flarb
    DROP CONSTRAINT DF__flarb__d__1B7E091A
GO
ALTER TABLE dbo.flarb
    DROP CONSTRAINT DF__flarb__e__1C722D53
GO
CREATE TABLE dbo.Tmp_flarb
    (
    a int NOT NULL,
    b int NOT NULL,
    d datetime NOT NULL,
    c timestamp NOT NULL,
    e uniqueidentifier NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_flarb SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_flarb ADD CONSTRAINT
    DF__flarb__d__1B7E091A DEFAULT (getdate()) FOR d
GO
ALTER TABLE dbo.Tmp_flarb ADD CONSTRAINT
    DF__flarb__e__1C722D53 DEFAULT (newsequentialid()) FOR e
GO
IF EXISTS(SELECT * FROM dbo.flarb)
     EXEC('INSERT INTO dbo.Tmp_flarb (a, b, d, e)
        SELECT a, b, d, e FROM dbo.flarb WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.blarf
    DROP CONSTRAINT FK__blarf__a__1E5A75C5
GO
DROP TABLE dbo.flarb
GO
EXECUTE sp_rename N'dbo.Tmp_flarb', N'flarb', 'OBJECT' 
GO
ALTER TABLE dbo.flarb ADD CONSTRAINT
    PK__flarb__3BD0198EF8B8CFAF PRIMARY KEY CLUSTERED 
    (
    a
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.flarb ADD CONSTRAINT
    CK__flarb__b__1A89E4E1 CHECK (([b]>(1)))
GO
CREATE TRIGGER dbo.flarb_tr
ON dbo.flarb
FOR UPDATE
AS
  SELECT 1;
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.blarf ADD CONSTRAINT
    FK__blarf__a__1E5A75C5 FOREIGN KEY
    (
    a
    ) REFERENCES dbo.flarb
    (
    a
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.blarf SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Yeah, that really looks like something I want to run in production, even if the table is small.

The other alternative is to do this quite manually. Say you have this table:

CREATE TABLE dbo.Employee
(
  EmployeeID INT,
  Name NVARCHAR(255),
  Salary INT
);
INSERT ...

Theoretically, and leaving out the complications caused by keys, constraints, triggers, and schemabound objects, you could "change" the order of the columns by - for each column to the left of the one you want to move - creating a new column, moving the data, dropping the old column, and renaming the new column. This works because the new column is always added to the "end" of the table. Leaving out error handling and any of the other complications:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
ALTER TABLE dbo.Employee ADD Nametmp NVARCHAR(255);
UPDATE dbo.Employee SET Nametmp = Name;
ALTER TABLE dbo.Employee DROP COLUMN Name;
EXEC sp_rename N'dbo.Employee.Nametmp', N'Name', N'COLUMN';
COMMIT TRANSACTION;

This of course has the potential to be as disruptive (or worse) as the Management Studio approach of just swapping out the entire table, and gets really complicated if you want to move more than one column.

Now, do you really need to change the column order?

5
  • Good points, but I'd disagree with your opener "You shouldn't care".I think you should care, because changing that column order could definitely impact dependent processes. For example, a procedure that has INSERT INTO yourtable (select a,b,c from y) would break or give unintended results if you change the column order of yourtable
    – Scotch
    May 4, 2020 at 16:21
  • @Scotch That was exactly my point. You shouldn't care about changing the order of columns in existing tables for precisely that reason. May 4, 2020 at 16:23
  • I guess I'm just interpreting you differently. I took that opener as "the column order is not something to be concerned about". But then you went on to give good reasons why it is something to be concerned about. I'd say you definitely SHOULD care about your column order and probably shouldn't change it. Maybe I'm being pedantic
    – Scotch
    May 4, 2020 at 16:33
  • @Scotch I don't know how to make sure people read the whole answer instead of interpreting the opening sentence in isolation. Better now? May 4, 2020 at 17:07
  • 1
    I realize this answer is very old, and I also realize this answer is 100% correct, but I take issue with "You shouldn't care". I use select * as part of my day to day workflow; not in applications, but as part of exploring a data set where I'm not sure what columns exist. Jul 13, 2021 at 14:25
2

You cannot change what appears to be the physical column order in SSMS without dropping and re-creating the table.

Why not just change the query to select the columns in a different order?

From:

SELECT Eid, Ename, Esalary FROM Table

To:

SELECT Eid, Esalary, Ename FROM Table
2
  • 2
    I largely agree, and up-voted, but you can do this without dropping the table, if you're very careful (see the last bit of my answer). Mar 28, 2014 at 14:19
  • Fair point, I hadn't thought about it like that. You'd have to be mad to want to do that on a large table...! Mar 28, 2014 at 14:25
2

I've recently published some code which can accomplish this. It can also drop/add identity constraints. I used this extensively for 6 years.

https://github.com/CaseyPlummer/mssql-table-rebuild

Reorder columns:

EXEC TableRebuild 'dbo','tblVersionHistory', 'VersionHistoryId,UpdateType,Version,Note,AppliedOn,AppliedBy'
2
  • this is basically a link only answer
    – nbk
    Mar 29, 2021 at 20:29
  • What is an appropriate amount of code to post? Just trying to help people when I have a solution to this exact problem. Mar 30, 2021 at 0:29
1

Do you want to change the underlying table, or just the resuls?

If it's just the results, then simply re-arrange your select statement.

eg. instead of

SELECT Eid, Ename, Esalary FROM ThatTable;

use

SELECT Eid, ESalary, EName FROM ThatTable;

You could also setup a view based on that table that selects in the desired order.

And of course if you really need to, you could simply drop and re-create the table. Just make sure to take care of exporting and importing the data if you want to keep it.

1

Tools > Options > Designers > Table and Database Designers > uncheck prevent saving changes...

After saving the settings you can now save changes made in design view including moving fields

0

It's not "EASY" possible. But you can create another one and insert the same data again, the way you want. The simplest way its to change operations statement order. Like:
SELECT Eid, Salary, Ename FROM TABLE;

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.