Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Difference between Cursor and While Loop with Example

Jul 14, 2014
Introduction:

Here I will explain difference between cursor and while loop in
sql server with example or explain cursor vs while loop with example in sql server. Cursors in sql server allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. While loop also same as cursor to fetch set of data and process each row in sql server.
Description:

Here it’s very difficult to say which one is better either cursor or while loop because both will do same thing they fetch set of data and process each row at a time. You need to choose either cursor or while loop based on your requirements.

We will check this with examples for that first create one table UserDetails in your database and insert some 100 or 200 records or install northwind database in your server and use orders table it contains more than 500 records

Now we will see the performance effect of using cursor and while loop

Cursor Example

Write cursor script like as shown below and run it. While running the query check execution time in right side


SET NOCOUNT ON
DECLARE ex_cursor CURSOR FOR SELECT OrderID,CustomerID FROM Orders
DECLARE @oid INT
DECLARE @cname NVARCHAR(50)
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
END
CLOSE ex_cursor
DEALLOCATE ex_cursor
Output:

When we run above query on Orders table in Northwind database query execution time it has taken 00 seconds check below image

 
While Loop Example

Write while loop script like as shown below and run it. While running the query check execution time in right side


DECLARE @Rows INT, @oid INT
DECLARE @cname NVARCHAR(50)
SET @Rows = 1
SET @oid = 0
WHILE @Rows > 0
BEGIN
SELECT TOP 1 @oid = OrderID, @cname = CustomerID FROM Orders WHERE OrderId >= @oid
SET @Rows = @@ROWCOUNT
PRINT  (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
SET @oid += 1
END
Output:

When we run above query on Orders table in Northwind database query execution time it has taken 00 seconds check below image

 

Based on above example both are doing same thing and taking same time to execute our script. You need to decide either while loop or cursor based on your requirement.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

4 comments :

Bhavik Patel said...

I think cursor use in more then one store procedure. best for re-usability.

Anonymous said...

you said that both are same, then whats the difference, I think that's the article heading.

Anonymous said...

Example problem - if some of the records have been deleted, then there will be missing OrderIds and stop early.
Most examples of while loops I have seen include creating a temporary table with a new primary index, filling it and stepping through it.

Ramzan Ali said...

Hi ! may if both are same so why have two name one is loop and other is cursor

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.