Use Temporary Tables Instead of Cursors in SQL Server

In Microsoft SQL Server, you frequently need to navigate through data row by row. This is usually done using T-SQL server-side cursors.

Considering that processing overhead on cursors is higher than on tables, I try to use a temporary table for such requirements. A table can be used for row-by-row operations, provided it has an unique identifier for each row. But this is not always true. Here is a technique to overcome that problem (example is based on a sales table of a pubs database):

 -- create the temporary table, adding a unique row identifierSELECT CONVERT(varchar(40), NEWID()) 'rowid', *INTO #tempFROM sales-- NEWID() is an T-SQL function that returns a unique value of type uniqueidentifier.-- An unique identifier looks like: 6F9619FF-8B86-D011-B42D-00C04FC964FF-- Now you can process one row at a time:DECLARE @rowid varchar(40)DECLARE @ord_date datetime-- Get first rowSELECT @rowid = MIN(rowid)FROM #tempWHILE @rowid IS NOT NULLBEGIN   -- Do something with the row data   SELECT @ord_date = ord_date   FROM #temp   WHERE rowid = @rowid   PRINT 'Row id: ' + @rowid + '  order date: ' + convert(varchar(10), @ord_date, 111)   -- Get next row id   SELECT @rowid = MIN(rowid)   FROM #temp   WHERE rowid > @rowidENDdrop table #temp
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: