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
Share the Post:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved