Can I logically reorder columns in a table?

Question

If I'm adding a column to a table in Microsoft SQL Server, can I control where the column is displayed logically in queries?

I don't want to mess with the physical layout of columns on disk, but I would like to logically group columns together when possible so that tools like SQL Server Management Studio list the contents of the table in a convenient way.

I know that I can do this through SQL Management Studio by going into their "design" mode for tables and dragging the order of columns around, but I'd like to be able to do it in raw SQL so that I can perform the ordering scripted from the command line.

Answer 1

You can not do this programatically (in a safe way that is) without creating a new table.

What Enterprise Manager does when you commit a reordering is to create a new table, move the data and then delete the old table and rename the new table to the existing name.

If you want your columns in a particular order/grouping without altering their physical order, you can create a view which can be whatever you desire.

Answer 2

I think what everyone here is missing is that although not everyone has to deal with 10's, 20's, or 1000's instances of the same software system installed throughout the country and world, those of us that design commercially sold software do so. As a result, we expand systems over time, expand tables by adding fields as new capability is needed, and as those fields are identified do belong in an existing table, and as such, over a decade of expanding, growing, adding fields, etc to tables, and then having to work with those tables from design, to support, to sometimes digging into raw data/troubleshooting to debug new functionality bugs, it is incredibly aggravating to not have the primary information you want to see within the first handful of fields, when you may have tables with 30, 40, 50, or even 90 fields, and yes, in a strictly normalized database.

I've often wished I could do this, for this exact reason. But short of doing exactly what SQL does, building a Create Script for a new Table the way I want it, writing the Insert to it, then dropping all existing constraints, relationships, keys, index, etc etc from the existing table and renaming the "new" table back to the old name, and then reading all those keys, relationships, index, etc etc ....

It's not only tedious, time-consuming, but ... in five more years, it will need to happen again.

It's so close to worth that massive amount of work, however the point is, it won't be the last time we need this ability, since our systems will continue to grow, expand, and get fields in a wacked ordered driven by need/design additions.

A majority of developers think from a single system standpoint that serves a single company or very specific hard box market.

The "off-the-shelf" but significantly progressive designers and leaders of development in their market space will always have to deal with this problem, over and over, and would love a creative solution if anyone has one. This could easily save my company a dozen hours a week, just not having to scroll over, or remember where "that" field is in the source data table.

Answer 3

When Management Studio does it, it's creating a temporary table, copying everything across, dropping your original table and renaming the temporary table. There's no simple equivalent T-SQL statement.

If you don't fancy doing that, you could always create a view of the table with the columns in the order you'd like and use that?

Edit: beaten!

Answer 4

If I understand your question, you want to affect what columns are returned first, second, third, etc in existing queries, right?

If all of your queries are written with SELECT * FROM TABLE - then they will show up in the output as they are laid out in SQL.

If your queries are written with SELECT Field1, Field2 FROM TABLE - then the order they are laid out in SQL does not matter.

Answer 5

There is one way, but its only temporarily for the query itself. For example,

Lets say you have 5 tables. Table is called T_Testing

FirstName, LastName, PhoneNumber, Email, and Member_ID

you want it to list their ID, then Last Name, then FirstName, then Phone then Email.

You can do it as per the Select.

Select Member_ID, LastName, FirstName, PhoneNumber, Email
From T_Testing

Other than that, if you just want the LastName to Show before first name for some reason, you can do it also as follows:

Select LastName, *
From T_Testing

The only thing you wanna be sure that you do is that the OrderBy or Where Function needs to be denoted as Table.Column if you are going to be using a Where or OrderBy

Example:

Select LastName, *
From T_Testing
Order By T_Testing.LastName Desc

I hope this helps, I figured it out because I needed to do this myself.

Answer 6

1. Script your existing table to a query window.
2. Run this script against a Test database (remove the Use statement)
3. Use SSMS to make the column changes you need
4. Click Generate Change Script (left most and bottommost icon on the buttonbar, by default)
5. Use this script against your real table

All the script really does is create a second table table with the desired column orders, copies all your data into it, drops the original table and then renames the secondary table to take its place. This does save you writing it yourself though should you want a deploy script.

Answer 7

It is not possible to change the order of the columns without recreating the whole table. If you have a few instances of the database only, you can use SSMS for this (Select the table and click "design").

In case you have too many instances for a manual process, you should try this script: https://github.com/Epaminaidos/reorder-columns

Answer 8

1. Open your table in SSMS in design mode:

   

2. Reorder your columns:

   

   It is important to not save your change.

3. Click the "Generate Change Script" button:

   

4. Now a window will open that contains the script to apply this change:

   

   Copy the text from the window.

In this instance, it generated the following code:

/* 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_MyTable
    (
    Id int NOT NULL,
    Name nvarchar(30) NULL,
    Country nvarchar(50) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_MyTable SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
     EXEC('INSERT INTO dbo.Tmp_MyTable (Id, Name, Country)
        SELECT Id, Name, Country FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT' 
GO
COMMIT

As you can see, what it does is 1) create a new temporary table, 2) copy the data over to the temporary table, 3) delete the original table and 4) rename the temporary table to the original table's name.

Answer 9

It can be done using SQL, by modifying the system tables directly. For example, look here:

Alter table - Add new column in between

However, I would not recommend playing with system tables, unless it's absolutely necessary.