I think this was a very useful article, because it highlighted a difference we sometimes forget between T-SQL and procedural code. Specifically, procedural code like

set value1 = value2

set value2 = value1

is executed as a set of statements which must be completed separately and in order. In a procedural language the above code would set the two variables equal to the same value, so a procedural programmer would naturally rewrite the code as

set @temp = value1

set value1 = value2

set value2 = @temp

In T-SQL, however, the code

update table

set value1 = value2,

value2 = value1

is a single statement, which means it happens all at once. So value1 will not be updated to =value2 until after the statement is complete (i.e, after value2 is =value1). Or, to simplify, in T-SQL, any references after the = in a set refer to the values before the statement executes.

As far as just renaming the columns, that works, but only if:

• All of the values need to be swapped. If, for example, you only need to swap some values because one data entry person got their fields mixed up, renaming columns will just make things worse.
• All code that accesses this table uses column names and not ordinal positions. I have seen code written by others which used the ordinal positions instead of the column names, and I myself have run into one situation (using VBScript and ADO) where for some reason it would not work using column names. (No, I didn't leave that using ordinal position -- I changed it to use ordinal position to get it working immediately, and then rewrote the routine when the system wasn't in use.)

So there are definitely reasons to know how to swap values, and it's good to be reminded how T-SQL allows for simpler coding of set-based operations than most procedural languages.