dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Apr 10, 2000

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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 identifier
SELECT CONVERT(varchar(40), NEWID()) 'rowid', *
INTO #temp
FROM 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 row
SELECT @rowid = MIN(rowid)
FROM #temp

WHILE @rowid IS NOT NULL
BEGIN
   -- 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 > @rowid
END

drop table #temp
Ravindra Okade
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date