avoiding cursors with sql server 2005 tech republic

Avoiding Cursors with SQL Server 2005 | TechRepublic

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412[08/29/2012 3:37:36 PM]

Blogs Downloads Newsletters Galleries Q&A Discussions News

Research Library

Home / Blogs / The Enterprise Cloud

The Enterprise Cloud

Avoiding Cursors with SQLServer 2005By Tim ChapmanJuly 21, 2008, 11:38 AM PDT

Takeaway: Sometimes cursors are necessary when executing queries in SQL Server, but mostof the time they can be avoided entirely. Tim Chapman shows where cursors can traditionally beused, and how you can use features packaged in SQL Server 2005 to avoid them.

How necessary are the use of cursors in SQL Server? In a previous article I showed you howyou could write your own custom procedures to loop through a collection of objects, such as tablesor databases, and execute statements against those objects. That article used cursors exclusivelyto loop through the objects. Sometimes cursors are necessary, especially when executing storedprocedures against separate records in a SQL Server table. However, when it comes to usingqueries to return result sets, most of the time cursors can be avoided entirely. Today I am going tolook at tricky problem where cursors would traditionally be used, and how you can use featurespackaged in SQL Server 2005 to avoid them.

The ProblemThe scenario I will look at today will involve an Inventory table, which holds information regardingintial beginning inventory per product, along with subsequent inventory transactions. To makethings a bit more simple, Ill assume that first record for a given date will contain the beginninginventory, each record after the initial record will indicate Inventory being moved.

The required report is a listing of the Products by Date and the amount of Inventory remaining atthe end of the day. Management wants to know when ineventory is getting low for specificproducts before the entire supply has been depleted. The code snippet below will create myInventory table and load some sample data into it.

IF OBJECT_ID('Inventory') IS NOT NULL

DROP TABLE Inventory;

GO

CREATE TABLE [dbo].Inventory

(

InventoryID [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Product] [varchar](150) NULL,

[InventoryDate] [datetime] NULL,

[InventoryCount] INT NULL

)

Follow this blog:

IT Management Development IT Support Data Center Networks Security

Log In Join TechRepublic FAQ Go Pro!ZDNet Asia SmartPlanet TechRepublic

http://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/d;44306;0-0;0;74341537;31-1/1;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/http://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/downloadshttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/photoshttp://www.techrepublic.com/forum/questionshttp://www.techrepublic.com/forum/discussionshttp://www.techrepublic.com/newshttp://www.techrepublic.com/research-libraryhttp://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/blog/datacenterhttp://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/j;44306;0-0;0;74341537;3823-300/100;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/search?q=tim+chapmanhttp://blogs.techrepublic.com.com/datacenter/?p=401http://www.techrepublic.com/blog/datacenter?mode=rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%3Fmode%3Drss&title=The+Enterprise+Cloud+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Favoiding-cursors-with-sql-server-2005%2F412&frequency=weekly&rss_type=38http://www.techrepublic.com/members/login?regSrc=global-reghttp://www.techrepublic.com/members/join?regSrc=global-reghttp://www.techrepublic.com/faq/generalhttp://www.techrepublic.com/prohttp://www.zdnetasia.com/http://www.smartplanet.com/http://www.techrepublic.com/



GO

INSERT INTO Inventory

(Product, InventoryDate, InventoryCount)

SELECT 'Computer', DATEADD(d, -5, GETDATE()), 5000

UNION ALL


UNION ALL


UNION ALL


UNION ALL




SELECT 'BigScreen', DATEADD(d, -5, GETDATE()), 5000

UNION ALL


UNION ALL


UNION ALL


UNION ALL




SELECT 'PoolTable', DATEADD(d, -5, GETDATE()), 5000

UNION ALL


UNION ALL


UNION ALL


UNION ALL


The tricky part about this report comes when trying to determine how much inventory is left aftereach transaction. For the PoolTable product above, there are 5000 units available for sale at theend of the first day. At the end of the second day, 4500 of those units have been sold, leavingonly 500 units. At the end of the third day, 3900 units have been sold, which means that moreinventory needs to be purchased. Because the next result is totally dependent on the preceedingresults, it makes sense to try to use a cursor to loop through each records and store values invariables and temp tables, and just report the result at the end. However, with some crafty TSQL,cursors can be avoided.

In the example below, I use a common-table expression (CTE) and the DENSE_RANK()windowing function, two new features in SQL Server 2005, to recurse through the results andreturn the final output as one TSQL statement.

;WITH RecursiveCTE(RowNumber, Product, InventoryCount, InventoryDate, RemainingInventory, Ranking)

AS

(

SELECT *

FROM

http://blogs.techrepublic.com.com/datacenter/?p=275



(

SELECT

RowNumber = CAST(DENSE_RANK() OVER(PARTITION BY product ORDER BY InventoryDate ASC) AS INT),

Product, InventoryCount, InventoryDate, ValueColumn = InventoryCount, Ranking = 0

FROM Inventory sho

) G

WHERE G.RowNumber = 1

UNION ALL

SELECT

r.RowNumber, r.Product, r.InventoryCount, r.InventoryDate, c.RemainingInventory - r.InventoryCount, c.Ranking + 1

FROM RecursiveCTE c

JOIN

(

SELECT RowNumber = CAST(DENSE_RANK() OVER(PARTITION BY Product ORDER BY InventoryDate ASC) AS INT),*

FROM Inventory

)r ON c.Product = r.Product AND r.RowNumber = c.RowNumber + 1

)

SELECT Product, InventoryDate, InventoryCount, RemainingInventory

FROM RecursiveCTE

ORDER BY Product, InventoryDate

Using a recursive CTE is not the only way to accomplish the desired results. You can also makeuse of subqueries to return the same thing. In fact, the subquery used below significantlyoutperforms the recursive CTE example mentioned above.

SELECT First.Product,First.InventoryDate, First.InventoryCount, Outage= 2*MAX(Second.InventoryCount)-SUM(Second.InventoryCount)

FROM

(

SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Product,InventoryDate ASC),*

FROM Inventory

) First

INNER JOIN

(

SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Product,InventoryDate ASC),*

FROM Inventory

) Second

ON First.Product = Second.Product AND First.RowNumber >= Second.RowNumber

GROUP BY

First.Product, First.InventoryDate, First.InventoryCount

ORDER BY

First.Product, First.InventoryDate ASC

Why not use a cursor?The two examples used above are by no means simple TSQL queries. You need a strongfoundation of TSQL and of some new SQL Server 2005 features to avoid cursors. But, why shouldyou avoid using a cursor for this problem? They are a little more simple to write and understandbecause all of the processing happens to one record at a time. However, the use of cursorsrequire more code to write, typically more memory to accomplish the same task, and cursors aretypically slower because they only handle one record at a time. The example code below usescursors to achieve the same as the two queries above, but at the cost of a lot more code and a lotslower execution time.



IF OBJECT_ID('tempdb..#InventoryTemp') IS NOT NULL

DROP TABLE #InventoryTemp

DECLARE @First BIT, @RemainingInventory INT

DECLARE @Product VARCHAR(20), @InventoryID INT, @InventoryCount INT

SELECT * INTO #InventoryTemp

FROM Inventory

ALTER TABLE #InventoryTemp

ADD RemainingInventory INT

DECLARE ProductCursor CURSOR FAST_FORWARD FOR

SELECT DISTINCT Product FROM Inventory

OPEN ProductCursor

FETCH NEXT FROM ProductCursor

INTO @Product

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT @First = 1

DECLARE InventoryCursor CURSOR FAST_FORWARD FOR

SELECT InventoryID, InventoryCount

FROM #InventoryTemp

WHERE Product = @Product

ORDER BY InventoryDate ASC

OPEN InventoryCursor

FETCH NEXT FROM InventoryCursor

INTO @InventoryID, @InventoryCounT

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @RemainingInventory = ISNULL(@RemainingInventory, @InventoryCount)

BEGIN

UPDATE #InventoryTemp

SET RemainingInventory = CASE WHEN @First = 1 THEN InventoryCount ELSE @RemainingInventory - @InventoryCount END

WHERE InventoryID = @InventoryID

SELECT @RemainingInventory = RemainingInventory

FROM #InventoryTemp

WHERE InventoryID = @InventoryID

END

SET @First = 0

FETCH NEXT FROM InventoryCursor

INTO @InventoryID, @InventoryCount

END

CLOSE InventoryCursor

DEALLOCATE InventoryCursor

FETCH NEXT FROM ProductCursor

INTO @Product

END

CLOSE ProductCursor

DEALLOCATE ProductCursor

SELECT * FROM #InventoryTemp



Join Login

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.

About Tim Chapman

Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.

ConclusionCursors arent all bad. In fact, they can make some database problems a lot easier to solve. But,the next time youre saddled with a problem and your first thought is to use a cursor to solve it,take a step back and really examine the problem. There is a chance that you can use a moreefficient set-based approach to solve your problem.

Full Bio Contact

Scripting out DHCPreservations in WindowsServer 2008 with Netsh

Windows Home ServerPower Pack 1 sees the lightof day

http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/blog/datacenter/scripting-out-dhcp-reservations-in-windows-server-2008-with-netsh/396http://www.techrepublic.com/blog/datacenter/scripting-out-dhcp-reservations-in-windows-server-2008-with-netsh/396http://www.techrepublic.com/blog/datacenter/scripting-out-dhcp-reservations-in-windows-server-2008-with-netsh/396http://www.techrepublic.com/blog/datacenter/scripting-out-dhcp-reservations-in-windows-server-2008-with-netsh/396http://www.techrepublic.com/blog/datacenter/windows-home-server-power-pack-1-sees-the-light-of-day/413http://www.techrepublic.com/blog/datacenter/windows-home-server-power-pack-1-sees-the-light-of-day/413http://www.techrepublic.com/blog/datacenter/windows-home-server-power-pack-1-sees-the-light-of-day/413http://www.techrepublic.com/blog/datacenter/windows-home-server-power-pack-1-sees-the-light-of-day/413

techrepublic.comAvoiding Cursors with SQL Server 2005 | TechRepublic

gtc3FsLXNlcnZlci0yMDA1LzQxMgA=: form1: q: button3:

avoiding cursors with sql server 2005 tech republic

Documents