SQL SERVER – Swap Column Values In Table

Some questions never got old and today’s we are going to discuss a similar question which was asked to me during Comprehensive Database Performance Health Check. The question was about how to swap column values in a table.

Let us first create a table with two columns and insert values in it.

CREATE TABLE TestTable (Col1 INT, Col2 INT);
INSERT INTO TestTable (Col1, Col2)
VALUES (1,2);
SELECT Col1, Col2
FROM TestTable;

In the table, there are two values 1 and 2 respectively in the Col1 and Col2.

The next task is to swap those values. If you are a developer and have learned the programming language in the class, you would think that you will need a third variable or a temporary storage location to swap the third value. However, if you are a SQL Server user, you can simply swap them using a single update statement.

SWAP Column

UPDATE TestTable
SET Col1 = Col2, Col2 = Col1;

When you run above update statement, the values of the columns will be swapped in SQL Server. There is no need for temporary column, variable or storage location in SQL Server. You can validate that with the SELECT statement here.

SELECT Col1, Col2
FROM TestTable;

Here is the final script to drop the table which you had created.

DROP TABLE TestTable;
GO

If you have any other interesting SQL Server Tip, do send me an email or leave a comment and I will be happy to post them on the blog with the due credit to you.

Here are my recent SQL in Sixty Seconds Video, let me know your thoughts about the same.

• Impact of DBCC DROPCLEANBUFFERS on Memory – SQL in Sixty Seconds #085
• Impact of CHECKPOINT On Memory – SQL in Sixty Seconds #084

To understand what actually CHECKPOINT and DBCC DROPCLEANBUFFERS do first we need to understand a couple of more concepts which are what is a clean buffer and dirty buffer in memory. I suggest you read the following blog post before continuing reading this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)