Swap columns

Question

Swap columns

Nakul Vachhrajani

SSChampion

Points: 10294
More actions
December 29, 2010 at 8:09 pm

#232818

Comments posted to this topic are about the item Swap columns
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

nicko5 SSC Enthusiast Points: 171 More actions · Answer 1

The execution of update statements are not evaluated left to right but as a batch after computations are complete which allows for them to be swapped.

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 2

I do not see a value for 2 points. It's only a simple swap. It behaves the same in any sqlserver release.

DECLARE @MyTable TABLE (Col1 INT,

Col2 INT)

-- Col1 Col2

INSERT INTO @MyTable VALUES (1, 2)

INSERT INTO @MyTable VALUES (3, 4)

UPDATE @MyTable SET Col1 = Col2, Col2 = Col1

SELECT Col1, Col2 FROM @MyTable

Try this for fun:

DECLARE @MyTable TABLE (Col1 INT,

Col2 INT

,Col3 INT)

DECLARE @i INT

-- Col1 Col2

INSERT INTO @MyTable(Col1,Col2) VALUES (1, 2)

INSERT INTO @MyTable(Col1,Col2) VALUES (3, 4)

SET @i = 0

UPDATE @MyTable SET

@i = @i + Col1

, Col1 = Col2

, Col2 = Col1

, Col3 = @i

SELECT Col1, Col2, Col3 FROM @MyTable

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Nice question, but I hope I will never need to swap data between two columns.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

jts2013 Hall of Fame Points: 3226 More actions · Answer 4

Nice Question - it's worth understanding this before you write UPDATE statements that you would expect to work in a procedural language, where the value of col1 would be overwritten by col2, meaning you couldn't then update col2 to the value of what col1 was before.

And yes, you do sometimes need to swap values between columns, or at least recalculate a column based on one or more other columns - usually when something has gone wrong!;-)

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

jts_2003 (12/30/2010)
...
usually when something has gone wrong!;-)

Exactly - as a matter of fact, that's when this question was thought of.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

jts_2003 (12/30/2010)
...usually when something has gone wrong!;-)

That's why I hope I will never need to use it 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

Interesting question. Hopefully I will never need to swap columns. 🙂

Rose Bud Hall of Fame Points: 3147 More actions · Answer 8

jts_2003 (12/30/2010)
Nice Question - it's worth understanding this before you write UPDATE statements that you would expect to work...

Definitely worthwhile. I did not know an update would work that way. Carlo's example provides further intrigue.

sknox SSChampion Points: 12388 More actions · Answer 9

This is how it should happen in any version of SQL server or any SQL database system. This is because SQL is declarative, not procedural. The difference is in the declarative, you're saying "put what's in column 1 into column 2 and what's in column 2 into column 1", whereas in the procedural you'd say "put what's in column 1 into column 2 and then what's in column 2 into column 1". A very tiny difference, but what a difference!

As for Carlos' example with a variable, that's an example of a quirky update, which I first learned of from here: http://www.sqlservercentral.com/articles/T-SQL/68467/ (which references http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/, which appears to be the original source of the term.)

Suffice to say it's a side-effect of the way Microsoft implemented a declarative language (SQL) within a procedural system (x86 software) -- one which works (when programmed meticulously under very specific circumstances), but isn't supported or guaranteed to continue to work in future iterations.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Carlo Romagnano (12/30/2010)
Try this for fun:
DECLARE @MyTable TABLE (Col1 INT,
Col2 INT
,Col3 INT)
DECLARE @i INT
-- Col1 Col2
INSERT INTO @MyTable(Col1,Col2) VALUES (1, 2)
INSERT INTO @MyTable(Col1,Col2) VALUES (3, 4)
SET @i = 0
UPDATE @MyTable SET
@i = @i + Col1
, Col1 = Col2
, Col2 = Col1
, Col3 = @i
SELECT Col1, Col2, Col3 FROM @MyTable

Mixing column swap with quirky update in this form is perhaps interesting, but certainly not deterministic - even though there are no indexes involved so that the only thing likely to disturb the sequencing is parallelism and doing this serially is probably going to be faster than the synchronisation overhead incurred in doing it in parallel so the optimiser is unlikely to choose parallel evaluation, there's nothing in the language specification that guarantees the order in which the rows are processed so the system can choose either row to process first or indeed can process both rows at once, which means there are 3 different results all of which would be "correct". So if you were relying on what goes into col3, you would need to add protective code to check that the (inter-row) sequencing is what you expected and abort the operation if it isn't.

Tom

UMG Developer SSChampion Points: 13482 More actions · Answer 12

Thanks for the question!

I think this would fall under the "Halloween Protection" portions of updates and deletes.

For some trivia you can read some about the Halloween problem here.

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 13

Tom.Thomson (12/30/2010)
Carlo Romagnano
Mixing column swap with quirky update in this form is perhaps interesting, but certainly not deterministic - even though there are no indexes involved so that the only thing likely to disturb the sequencing is parallelism and doing this serially is probably going to be faster than the synchronisation overhead incurred in doing it in parallel so the optimiser is unlikely to choose parallel evaluation, there's nothing in the language specification that guarantees the order in which the rows are processed so the system can choose either row to process first or indeed can process both rows at once, which means there are 3 different results all of which would be "correct". So if you were relying on what goes into col3, you would need to add protective code to check that the (inter-row) sequencing is what you expected and abort the operation if it isn't.

Here a real situation for using local variable in the update statement.

-- prologue

CREATE TABLE toProcTable

(

ID int identity(1,1) NOT NULL primary key clustered

,cCol1 sysname

,bProcessed BIT

)

CREATE INDEX IDX__toProcTable ON toProcTable (bProcessed)

-- in sql2008, add here a filter bProcessed IS NULL OR bProcessed = 0

DECLARE @id INT

,@cCol1 sysname

INSERT INTO toProcTable(cCol1)

SELECT name from sys.objects

-- end prologue

-- process one row at time

WHILE 1=1

BEGIN

BEGIN TRAN

-- in the table a list of rows to process one after one, doesn't matter in which order

UPDATE TOP (1) toProcTable SET

bProcessed = 1

-- save some info for later processing

,@id = toProcTable.ID

,@cCol1 = toProcTable.cCol1

WHERE bProcessed IS NULL

OR bProcessed = 0

IF @@ROWCOUNT < 1

BREAK

-- do some process

PRINT 'Processing: ' + @cCol1

SELECT * FROM sys.tables s WHERE s.name = @cCol1

COMMIT

END

DROP TABLE toProcTable

sknox SSChampion Points: 12388 More actions · Answer 14

Carlo Romagnano (12/31/2010)

Here a real situation for using local variable in the update statement.

Carlo,

I'm curious as to the context of your code (not included here for brevity and because somewhere between me and SQLServerCentral.com is a stupid IPS that thinks any text with a certain common SQL word in it is an injection attack) -- I'm guessing you abbreviated the row process in what you posted from some form of dynamic SQL.

I'm curious for an explanation of why you're doing row-by-row processing here instead of set-based operations.

Also, have you tested this implementation against a cursor-based implementation?