How to Change Column Order in a Table Using SQL Query in SQL Server 2005

How to change column order in a table using sql query in sql server 2005?

You cannot. The column order is just a "cosmetic" thing we humans care about - to SQL Server, it's almost always absolutely irrelevant.

What SQL Server Management Studio does in the background when you change column order there is recreating the table from scratch with a new CREATE TABLE command, copying over the data from the old table, and then dropping it.

There is no SQL command to define the column ordering.

How To change the column order of An Existing Table in SQL Server 2008

I got the answer for the same ,
Go on SQL Server → Tools → Options → Designers → Table and Database Designers and unselect Prevent saving changes that require table re-creation

2- Open table design view and that scroll your column up and down and save your changes.

Changing the Order of Columns in a Table Permanently Using T-SQL query

There's no way to do what you want without dropping and readding the table. To see how Management Studio accomplishes this (at least in 2012, but I think this applies to 2005 too), go to Management Studio's Options > Designers > Table and Database Designer > Uncheck Prevent saving changes that require table re-creation.

Once you do that you can go in to the table designer, change the order of the columns and instead of clicking "Save", click "Save Change Script" to see the SQL.

Here is what SQL 2012 would generate:

/* 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_Table_1
    (
    TEst1 nchar(10) NULL,
    Test3 nchar(10) NULL,
    Test2 nchar(10) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table_1 SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Table_1)
     EXEC('INSERT INTO dbo.Tmp_Table_1 (TEst1, Test3, Test2)
        SELECT TEst1, Test3, Test2 FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Table_1
GO
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT' 
GO
COMMIT

SQL Server 2005: Update rows in a specified order (like ORDER BY)?

UPDATE statements will be executed as a single query, not as a step by step result.

You need to either use a while loop/cursor (uhhgg) or maybe make use of a CTE expression view to achieve what you are trying, which gives you the recursice possibility.

Have a look at

• Using Common Table Expressions
• Recursive Queries Using Common Table
  Expressions

SQL Server 2016: Change Column Order in Data Warehouse

Is there any option in SQL server 2016 to change column order still, without creating a temp table, and recreating/reinserting whole table?

No. Column ordinals in SQL Server control the visible order of the columns and the physical layout of the data.

SQL Server 2005 - ALTER TABLE AFTER COLUMN

You can only add columns at the end.

And even that will use a schema modify lock for a short time, so in a very sensitive production environment, you should be aware of this.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

Changing the column order/adding new column for existing table in SQL Server 2008

One possibility would be to not bother about reordering the columns in the table and simply modify it by add the columns. Then, create a view which has the columns in the order you want -- assuming that the order is truly important. The view can be easily changed to reflect any ordering that you want. Since I can't imagine that the order would be important for programmatic applications, the view should suffice for those manual queries where it might be important.

---

Related Topics