Cursor operator in SQL Server Execution plan

The Cursor Operator appears in an execution plan when a query is executed within a cursor. Cursors are used for row-by-row processing of data, which can be useful for complex row-level operations that aren’t easy to achieve with standard SQL statements. However, cursors generally have performance drawbacks because SQL Server is optimized for set-based operations, not row-by-row processing.

Open cursor

 

Fetch cursor

For the demo we are creating a table and inserting some records.

CREATE TABLE EmployeeSales (     EmployeeID INT,     SaleAmount DECIMAL(10, 2),     SaleDate DATE );  INSERT INTO EmployeeSales (EmployeeID, SaleAmount, SaleDate) VALUES     (1, 100.00, '2022-01-01'),     (2, 200.50, '2022-01-02'),     (1, 150.00, '2022-01-03'),     (3, 300.00, '2022-01-04'),     (2, 250.00, '2022-01-05');

Now writing cursor to fetch the data

-- Declare variables for cursor processing DECLARE @EmployeeID INT,         @SaleAmount DECIMAL(10, 2),         @TotalSales DECIMAL(10, 2) = 0; -- Declare cursor DECLARE sales_cursor CURSOR FOR   SELECT employeeid,          saleamount   FROM   employeesales   ORDER  BY employeeid; -- Open cursor OPEN sales_cursor; -- Fetch each row from the cursor FETCH next FROM sales_cursor INTO @EmployeeID, @SaleAmount; WHILE @@FETCH_STATUS = 0   BEGIN       -- Calculate running total for each employee       SET @TotalSales = @TotalSales + @SaleAmount;       PRINT 'EmployeeID: '             + Cast(@EmployeeID AS VARCHAR(10))             + ', Total Sales: '             + Cast(@TotalSales AS VARCHAR(20));       -- Fetch the next row       FETCH next FROM sales_cursor INTO @EmployeeID, @SaleAmount;   END; -- Close and deallocate cursor CLOSE sales_cursor; DEALLOCATE sales_cursor;

 Running this script and see the execution plan

Cursor is expensive so we need to avoid cursor.