Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Cursors in sql server - Part 63

Suggested Videos
Part 60 - Correlated subquery
Part 61 - Creating a large table with random data for performance testing
Part 62 - What to choose for performance - SubQuery or Joins

Relational Database Management Systems, including sql server are very good at handling data in SETS. For example, the following "UPDATE" query, updates a set of rows that matches the condition in the "WHERE" clause at the same time. 
Update tblProductSales Set UnitPrice = 50 where ProductId = 101

However, if there is ever a need to process the rows, on a row-by-row basis, then cursors are your choice. Cursors are very bad for performance, and should be avoided always. Most of the time, cursors can be very easily replaced using joins.

There are different types of cursors in sql server as listed below. We will talk about the differences between these cursor types in a later video session. 
1. Forward-Only
2. Static
3. Keyset
4. Dynamic 

Let us now look at a simple example of using sql server cursor to process one row at time. We will be using tblProducts and tblProductSales tables, for this example. The tables here show only 5 rows from each table. However, on my machine, there are 400,000 records in tblProducts and 600,000 records in tblProductSales tables. If you want to learn about generating huge amounts of random test data, please watch Part - 61 in sql server video tutorial.

Cursor Example: Let us say, I want to update the UNITPRICE column in tblProductSales table, based on the following criteria
1. If the ProductName = 'Product - 55', Set Unit Price to 55
2. If the ProductName = 'Product - 65', Set Unit Price to 65
3. If the ProductName is like 'Product - 100%', Set Unit Price to 1000

Declare @ProductId int

-- Declare the cursor using the declare keyword
Declare ProductIdCursor CURSOR FOR 
Select ProductId from tblProductSales

-- Open statement, executes the SELECT statment
-- and populates the result set
Open ProductIdCursor

-- Fetch the row from the result set into the variable
Fetch Next from ProductIdCursor into @ProductId

-- If the result set still has rows, @@FETCH_STATUS will be ZERO
While(@@FETCH_STATUS = 0)
Declare @ProductName nvarchar(50)
Select @ProductName = Name from tblProducts where Id = @ProductId

if(@ProductName = 'Product - 55')
Update tblProductSales set UnitPrice = 55 where ProductId = @ProductId
else if(@ProductName = 'Product - 65')
Update tblProductSales set UnitPrice = 65 where ProductId = @ProductId
else if(@ProductName like 'Product - 100%')
Update tblProductSales set UnitPrice = 1000 where ProductId = @ProductId

Fetch Next from ProductIdCursor into @ProductId

-- Release the row set
CLOSE ProductIdCursor 
-- Deallocate, the resources associated with the cursor
DEALLOCATE ProductIdCursor

The cursor will loop thru each row in tblProductSales table. As there are 600,000 rows, to be processed on a row-by-row basis, it takes around 40 to 45 seconds on my machine. We can achieve this very easily using a join, and this will significantly increase the performance. We will discuss about this in our next video session.

To check if the rows have been correctly updated, please use the following query.
Select  Name, UnitPrice 
from tblProducts join
tblProductSales on tblProducts.Id = tblProductSales.ProductId
where (Name='Product - 55' or Name='Product - 65' or Name like 'Product - 100%')

1 comment:

  1. Hello Venkat Sir can you upload some lectures on four types of cursors as you described in this video


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.