devxlogo

Use Temporary Tables Instead of Cursors in SQL Server

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

devx-admin

Share the Post: