SQL Server - Difference between Cursor and While Loop with Example

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:

In previous articles I explained While loop example in sql server, nested while loop example in sql server SQL Server change identity column value,  replace multiple spaces with single space in sql, reseed identity column value in sql server, get only month and year from date in sql server, substring function in SQL server and many articles relating to SQL server. Now I will explain difference between cursor and while loop in sql server.

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 Email