How To Change Column Position In SQL Server

Recently, I was required to change the column position in my SQL server table. As a senior SQL developer, I have analyzed all the possible approaches. In this article, I will discuss each approach.

Approach-1: Using the SSMS design approach

Follow the below steps.

1. Open SQL Server Management Studio [SSMS].

2. Right-click on the table name and then select the Design option.

3. Click on the Arrow symbol on the left side of the record to select that record.

4. Press and hold the left mouse, click, and drag it to the required position. The column will be successfully moved to the required position, as shown in the screenshot below.

5. Finally, click the Save button to save the changes.

Check out How to Export SQL Server Table to CSV

Approach 2: Using a View

We can also create a view with the required column order and execute the query below.

CREATE VIEW vw_AzureProduct AS
SELECT 
    ProductID,
    ProductBrand,
    ProductName
FROM AzureProduct;

The view was created successfully after executing the above query, as shown in the screenshot below.

We can execute the query below to confirm if the view was created successfully.

SELECT * FROM sys.views WHERE name = 'vw_AzureProduct'

Check out How To Check User Permissions On Table In SQL Server

Approach 3: Recreating the table

First, we need to create a new table with a different name with the column order as per your requirement. You can use the query below.

CREATE TABLE AzureProductNew (
    ProductID INT,
	ProductBrand nVARCHAR(50),
    ProductName nVARCHAR(50)
   
);

After executing the above query, I got the expected output below.

Now, we use the below query to copy the data from the old table to the new one.

INSERT INTO AzureProductNew (ProductID, ProductBrand, ProductName)
SELECT ProductID, ProductBrand, ProductName
FROM AzureProduct;

As a next step, we need to drop the old table using the query, as shown in the screenshot below.

As a final step, we need to rename the table using the below query.

EXEC sp_rename 'AzureProductNew', 'AzureProduct';

After executing the above query, we got the expected output as shown below.

Conclusion

SQL Server doesn’t provide any direct query to change the column order but possible with different approaches mentioned in this article. You can use the approach as per your requirement.

You may also like following the articles below

• How To Find A Table In SQL Server
• How To Duplicate A Table In SQL Server