Cursor in SQL Server with Real-Time Scenario

In SQL Server, handling row-by-row operations efficiently can be challenging. This is where a Cursor in SQL Server comes into play. Unlike standard SQL operations that work with sets of data, cursors allow developers to manipulate data one row at a time. In this article, we will explore cursors in depth, explain how they work, provide real-time scenarios, and give practical code examples for beginners and intermediate learners.

What is a Cursor in SQL Server?

A Cursor in SQL Server is a database object that allows traversal over the rows of a result set, enabling row-by-row processing. Unlike typical SQL operations, which are set-based, cursors are procedural in nature.

Key Characteristics of SQL Server Cursors

• Used to fetch rows sequentially from a result set.
• Supports operations like INSERT, UPDATE, and DELETE on individual rows.
• Ideal for complex operations where set-based SQL is difficult or inefficient.

Types of Cursors in SQL Server

SQL Server provides different types of cursors based on the behavior and performance requirements:

When to Use Cursor in SQL Server

While cursors can be slower than set-based operations, they are useful in the following scenarios:

• Processing complex row-by-row calculations.
• Generating sequential reports.
• Performing operations that depend on previous rows.
• Updating rows conditionally based on dynamic criteria.

SQL Server Cursor Syntax

📄

DECLARE cursor_name CURSOR 
FOR 
SELECT column1, column2 
FROM table_name 
WHERE condition;

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO @variable1, @variable2;

WHILE @@FETCH_STATUS = 0
BEGIN
   -- Your row-wise processing logic
   FETCH NEXT FROM cursor_name INTO @variable1, @variable2;
END

CLOSE cursor_name;
DEALLOCATE cursor_name;

Real-Time Scenario: Cursor in SQL Server

Consider a scenario where a company wants to calculate bonus for employees based on individual sales targets. A row-by-row calculation is required because the bonus depends on cumulative performance.

Step-by-Step Example

📄

-- Create Employee table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50),
    SalesAmount DECIMAL(10,2),
    Bonus DECIMAL(10,2) DEFAULT 0
);

-- Insert sample data
INSERT INTO Employee (EmployeeID, Name, SalesAmount) VALUES
(1, 'Alice', 12000),
(2, 'Bob', 8000),
(3, 'Charlie', 15000);

-- Declare variables for cursor
DECLARE @EmpID INT, @Sales DECIMAL(10,2), @Bonus DECIMAL(10,2);

-- Declare cursor for employee bonus calculation
DECLARE EmpCursor CURSOR FOR
SELECT EmployeeID, SalesAmount FROM Employee;

OPEN EmpCursor;

FETCH NEXT FROM EmpCursor INTO @EmpID, @Sales;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Calculate bonus: 10% for sales above 10000, 5% otherwise
    IF @Sales > 10000
        SET @Bonus = @Sales * 0.10;
    ELSE
        SET @Bonus = @Sales * 0.05;

    -- Update the bonus column
    UPDATE Employee
    SET Bonus = @Bonus
    WHERE EmployeeID = @EmpID;

    FETCH NEXT FROM EmpCursor INTO @EmpID, @Sales;
END

CLOSE EmpCursor;
DEALLOCATE EmpCursor;

-- Verify updated data
SELECT * FROM Employee;

Explanation: This example demonstrates a real-world use case where bonuses are calculated row-by-row using a cursor. The cursor loops through each employee, calculates the bonus based on individual sales, and updates the table.

Advantages of Using SQL Server Cursors

• Row-level processing is possible when set-based logic is not feasible.
• Supports complex business logic.
• Can be used to interact with multiple tables sequentially.

Disadvantages of SQL Server Cursors

• Performance overhead compared to set-based SQL operations.
• Consumes more server memory and resources.
• Should be used only when necessary.

Cursors in SQL Server are powerful tools for row-by-row processing and complex business logic implementation. While they may introduce performance overhead, they are indispensable in scenarios where set-based SQL cannot handle the required logic. By understanding the types of cursors, best practices, and real-world examples, developers can leverage cursors effectively for SQL Server applications.

Frequently Asked Questions (FAQs)

1. What is the difference between a cursor and a loop in SQL Server?

A cursor is a database object that allows traversal over rows in a result set, whereas a loop (like WHILE) is a procedural construct. Cursors provide easier row-wise data manipulation and support operations like FETCH, OPEN, and CLOSE, which loops do not.

2. Are cursors slow in SQL Server?

Cursors can be slower than set-based operations due to row-by-row processing. However, they are useful when each row requires unique operations that cannot be performed in a set-based query.

3. What types of cursors are available in SQL Server?

SQL Server provides Static, Dynamic, Forward-Only, and Keyset-Driven cursors. Each type has different characteristics regarding performance and how it handles underlying data changes.

4. How do I improve cursor performance?

Use Forward-Only and Read-Only cursors when possible, avoid unnecessary updates, close and deallocate cursors after use, and consider set-based alternatives where feasible.

5. Can cursors be used for large datasets?

Yes, but performance may degrade with large datasets. For very large datasets, consider using batch processing, temporary tables, or set-based SQL operations instead of cursors.