SQL Interview Questions

A cursor in SQL is a database object used to retrieve, manipulate, and navigate through a result set one row at a time. Cursors are particularly useful for operations that require row-by-row processing, which is not easily achievable with standard SQL set-based operations.

Key Characteristics of Cursors

1. Row-by-Row Processing: Allows processing of query results one row at a time.
2. Control over Navigation: Provides control over the navigation through the result set with operations such as fetching, moving to the next row, or moving to the previous row.
3. Manipulation of Retrieved Data: Allows manipulation of individual rows fetched from the result set.

Types of Cursors

1. Implicit Cursors: Automatically created by the database when a SQL statement is executed. These are typically used for single-row queries.
2. Explicit Cursors: Defined and controlled by the programmer for processing multi-row queries.

Lifecycle of a Cursor

1. Declaration: Define the cursor with the specific query.
2. Opening: Allocate resources and execute the query to establish the result set.
3. Fetching: Retrieve individual rows from the result set.
4. Closing: Release the resources associated with the cursor.

Example of Using a Cursor

Here is an example using a cursor in a stored procedure to iterate over a result set in SQL Server.

Step 1: Declare the Cursor

DECLARE @EmployeeID INT;
DECLARE @EmployeeName VARCHAR(50);

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, EmployeeName
FROM Employees
WHERE DepartmentID = 1;

Step 2: Open the Cursor

OPEN employee_cursor;

Step 3: Fetch the Rows

FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process each row
    PRINT 'Employee ID: ' + CAST(@EmployeeID AS VARCHAR) + ', Employee Name: ' + @EmployeeName;

    -- Fetch the next row
    FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName;
END;

Step 4: Close and Deallocate the Cursor

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Detailed Explanation

1. Declaration:

   • The cursor employee_cursor is declared to select EmployeeID and EmployeeName from the Employees table where DepartmentID is 1.

2. Opening:

   • The OPEN statement executes the cursor's query and prepares the result set.

3. Fetching:

   • The FETCH NEXT statement retrieves the next row from the cursor into the specified variables (@EmployeeID and @EmployeeName).
   • The WHILE loop continues to fetch rows while there are rows to fetch (@@FETCH_STATUS returns 0 if the fetch was successful).

4. Closing and Deallocation:

   • The CLOSE statement releases the result set.
   • The DEALLOCATE statement removes the cursor definition and releases the resources associated with it.

Advantages of Cursors

1. Fine-Grained Control: Provides detailed control over row-by-row processing and manipulation.
2. Complex Operations: Useful for complex operations that require procedural logic and cannot be easily expressed with set-based SQL operations.
3. Sequential Processing: Ideal for operations that require sequential processing of rows.

Disadvantages of Cursors

1. Performance Overhead: Cursors can be slow and resource-intensive, as they process rows individually rather than in a set-based manner.
2. Complexity: Adds complexity to SQL code and can be harder to maintain and debug.
3. Concurrency Issues: Potentially more prone to concurrency issues in a multi-user environment.

Best Practices

1. Limit Use: Use cursors sparingly and only when necessary. Prefer set-based operations for performance reasons.
2. Minimize Scope: Declare cursors with the smallest possible scope to reduce resource usage.
3. Optimize Queries: Ensure the cursor's query is optimized to minimize the result set and improve performance.

Conclusion

Cursors in SQL provide a mechanism for row-by-row processing of query results, allowing for fine-grained control and complex operations that are not feasible with standard set-based SQL. However, they come with performance and complexity trade-offs, so they should be used judiciously and optimized carefully to ensure efficient database operations.