Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Swap columns


Swap columns

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21067 Visits: 18258
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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10696 Visits: 11991
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3598 Visits: 3232
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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 2709
[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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1656 Visits: 3018
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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8317 Visits: 11544
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7821
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
    SSChasing Mays
    SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

    Group: General Forum Members
    Points: 635 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