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