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
See also  How to Create and Deploy QR Codes Online: A Comprehensive Guide

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist