Change Column Order in a Table

  • Artikel

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

You can change the order of columns in Table Designer in SQL Server Management Studio (SSMS). By default, a safety mechanism of SSMS blocks changing the column order. Though it isn't recommended, you can change the column order in a table by re-creating the table.

Adding columns to a table by default adds them to the end of the table, as is recommended.

Recommendations

Best practices with table column order:

  • To change the order of columns displayed in a result set, report, or application, use the column order in a SELECT (Transact-SQL) statement. Always specify the columns by name in your queries and applications in the order in which you would like them to appear.
  • Don't use SELECT * in applications. Added or removed columns could cause unexpected behavior or errors in applications.
  • Add new columns to the end of tables.

Caution

Changing the column order of a table may affect code and applications that depend on the specific order of columns. These include queries, views, stored procedures, user-defined functions, and client applications. Carefully consider any changes you want to make to column order.

Use SQL Server Management Studio

Change the column order

Though not recommended, you can change the order of columns in a table using SQL Server Management Studio (SSMS). This requires recreating the table.

Important

Always use the latest version of SQL Server Management Studio (SSMS).

  1. In Object Explorer, right-click the table with columns you want to reorder and select Design.

  2. Select the box to the left of the column name that you want to reorder.

  3. Drag the column to another location within the table.

You may be blocked making these changes by an important safety feature of SSMS, controlled by the setting Prevent saving changes that require table re-creation. This setting is enabled to prevent accidental drop/recreate of the table via SSMS dialogues, which may be a disruptive and result in the loss of metadata or permissions. For more information, see "Saving changes is not permitted" error message in SSMS. Instead, it's recommended you execute these type of changes, with full awareness of their impact, via Transact-SQL steps that account for permissions and metadata.

Caution

Re-creating a table will block concurrent access to the table for other users and applications. For large tables, this could require a long duration and a large amount of transaction log space.

Use Transact-SQL

Changing column order isn't supported using Transact-SQL statements. The table must be dropped and recreated in order to change column order.

Remarks

To query existing columns, use the sys.columns object catalog view.

Next steps