Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Swap columns Expand / Collapse
Author
Message
Posted Thursday, December 30, 2010 9:36 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
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
Post #1040980
Posted Thursday, December 30, 2010 9:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #1040989
Posted Thursday, December 30, 2010 11:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #1041096
Posted Friday, December 31, 2010 2:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,559, Visits: 2,418
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

Post #1041317
Posted Friday, December 31, 2010 7:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 1,339, Visits: 1,715
[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?
Post #1041388
Posted Friday, December 31, 2010 8:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:37 AM
Points: 1,510, Visits: 2,708
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".
Post #1041406
Posted Sunday, January 2, 2011 3:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 6,040, Visits: 8,322
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
Post #1041669
Posted Wednesday, January 5, 2011 7:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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?
Post #1043453
Posted Friday, January 7, 2011 6:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 1,796, Visits: 5,795
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1044821
    Posted Tuesday, January 18, 2011 6:07 AM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Sunday, November 17, 2013 11:53 AM
    Points: 623, Visits: 237
    Question title provided answer for the question
    Post #1049264
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse