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

Tom.Thomson (12/30/2010)

---

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.

-- 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 

Koen (da-zero) (12/30/2010)

---

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

Koen (da-zero) (12/30/2010)

---

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