SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Swap columns


Swap columns

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143291 Visits: 18649
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
Learn Extended Events

Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50317 Visits: 13147
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
UMG Developer
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7200 Visits: 2204
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
Carlo Romagnano
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12287 Visits: 3514
Tom.Thomson (12/30/2010)
[b]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
sknox
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6888 Visits: 3153
[quote]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?
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7585 Visits: 3146
Koen (da-zero) (12/30/2010)
Nice question, but I hope I will never need to swap data between two columns.

You might need to swap if you have a pair of columns such as first_name, last_name and the user enters them in reverse, e.g. "Thomas Mitchell" when it should have been "Mitchell Thomas".
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34139 Visits: 13104
Nice question. Though I'm surprised at the high percentage of incorrect answers.

Also, the disclaimer can be removed. This behaviour of the UPDATE statement is how the ANSI standard prescribes it to behave, Any other result, in any version of SQL Server (or even any other relational database) would qualify as a bug. And a severe one.
THe UPDATE should work as if all the results are computed first (based on the old information), and then all rows are updated with the new values instantaneously. That's not actually possible in current technology, but the effect should be the same as if it were possible.


Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
john.arnott
john.arnott
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7042 Visits: 3059
Koen (da-zero) (12/30/2010)
jts_2003 (12/30/2010)
...usually when something has gone wrong!;-)


That's why I hope I will never need to use it ;-)

Although you may never have to simply swap the values of two columns, isn't it valuable to know that the original value of a column remains available within an UPDATE even following code that would appear to modify it?
mister.magoo
mister.magoo
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26272 Visits: 7939
Nice question - although the title does give the answer!

I also wonder about points allocation on these questions - although I don't really care about it - but it seems almost random!

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Dhruvesh Shah
    Dhruvesh Shah
    Ten Centuries
    Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

    Group: General Forum Members
    Points: 1209 Visits: 237
    Question title provided answer for the question :-D
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum







































































































































































    SQLServerCentral


    Search