SQL Server - Difference between Cursor and While Loop with Example

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.