KEYSET Cursor in SQL Server

The KEYSET Cursor in SQL Server can only move from the first row to last, and last to first. When the cursor is open, the order of the rows and the membership is fixed. And the set of a key that identifies the rows uniquely will be stored in a table under tempdb database.

Remember, to use the SQL Keyset cursor on a table with a unique key, otherwise, this cursor will act like any other static cursor.

Once you Open the SQL KEYSET Cursor on Employee, any INSERT operations on that table will not be reflected in the Cursor but both DELETE and UPDATE operations will be reflected.

In this article we will show you, How to Create a keyset Cursor in SQL Server with example.

For this demonstration, We are going to use the below-shown table. As you can see from the below screenshot that our Employee table holds 14 records

KEYSET Cursor in SQL Server Example 1

In this example, we will show you, How to declare and open a keyset cursor in SQL Server, and how to fetch rows from the cursor. In this example, we will use the WHILE LOOP to loop over the cursor elements and print them as output.

Remember, if you apply keyset cursor on any table that does not have at least one unique column then it will act as STATIC CURSOR

SET NOCOUNT ON
-- Declaring the Variables 
DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

DECLARE keyset_employee_cursor CURSOR 
KEYSET FOR 
	SELECT [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable

OPEN keyset_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
	FETCH NEXT FROM keyset_employee_cursor 
              INTO @EmpID, @EmpName, @EmpEducation,
		       @EmpOccupation, @EmpYearlyIncome, @EmpSales
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
			+', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
			+ ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
			+ ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)
		
                FETCH NEXT FROM keyset_employee_cursor
                      INTO @EmpID, @EmpName, @EmpEducation,
				   @EmpOccupation, @EmpYearlyIncome, @EmpSales
	END
END
CLOSE keyset_employee_cursor
DEALLOCATE keyset_employee_cursor
SET NOCOUNT OFF

OUTPUT

If you observe the cursor declaration carefully, we are using KEYSET cursor, while declaring the cursor. We already explained the remaining steps in our previous article, I suggest you refer Static Cursor in SQL Server example.

Let me add the WAITFOR DELAY ’00:00:05′ to delay the execution of the query, and PRINT @@FETCH_STATUS to print the FETCH status (-2 to 0)

KEYSET Cursor in SQL Server Example 2

In this example, we will show you, what will happen when you perform INSERT operation while the Cursor is Open.

SET NOCOUNT ON
-- Declaring the Variables 
DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

DECLARE keyset_employee_cursor CURSOR 
KEYSET FOR 
	SELECT TOP 5 [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable

OPEN keyset_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
	FETCH NEXT FROM keyset_employee_cursor 
              INTO @EmpID, @EmpName, @EmpEducation,
		       @EmpOccupation, @EmpYearlyIncome, @EmpSales
	WHILE @@FETCH_STATUS = 0
	BEGIN
                WAITFOR DELAY '00:00:05'
		PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
			+', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
			+ ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
			+ ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)
		
                FETCH NEXT FROM keyset_employee_cursor
                      INTO @EmpID, @EmpName, @EmpEducation,
				   @EmpOccupation, @EmpYearlyIncome, @EmpSales
                PRINT @@FETCH_STATUS
	END
END
CLOSE keyset_employee_cursor
DEALLOCATE keyset_employee_cursor
SET NOCOUNT OFF

while the Cursor is Open, let me insert few records using the following query

USE [SQL Tutorial]
GO
INSERT INTO [EmployeeTable] (
		[Name]
	       ,[Education]
	       ,[Occupation]
	       ,[YearlyIncome]
	       ,[Sales]
	     )
VALUES ('Imran Khan', 'Bachelors', 'Skilled Professional', 69000, 100)
      ,('Doe Lara', 'Bachelors', 'Management', 85000, 60)
      ,('Ramesh Kumar', 'High School', 'Professional', 45000, 630)
      ,('John Ruiz', 'Partial High School', 'Clerical', 40000, 220)

OUTPUT

If you observe the below screenshot, though we have inserted four new records into our Employee table, the keyset cursor is returning the top 5 records. This is because, once the cursor is open, it will fix the key value, and an insert operation by another user will not be reflected inside the cursor.

Please use the following SQL Query to check whether the new records that are inserted into the Employee table or not.

USE [SQL Tutorial]
GO
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

OUTPUT

KEYSET Cursor in SQL Server Example 3

In this example, we will show you, what will happen when you perform UPDATE operation while the Cursor is Open. For this, we are using the cursors definition that we used in our previous example, and here we are selecting all the columns instead of Top 5.

While the Cursor is Open, let me Update the yearly Income and Sales amount of the Employees whose occupation is management using the following query

USE [SQL Tutorial]
GO
UPDATE [EmployeeTable] 
    SET [YearlyIncome] = 999999,
        [Sales] = 15000
WHERE [Occupation] = N'Management'

OUTPUT

If you observe the below screenshot, though the keyset cursor is open, it is showing the Updated values from our Employee table. This is because, even though the cursor is open, it can read the updated values (values updated by another user).

Let me check the Employee table, whether the Update has happened at the server level or not.

KEYSET Cursor in SQL Server Example 4

In this example, we will show you, what will happen when you perform DELETE operation while the Cursor is Open. For this, we are using the cursors definition that we used in our previous example.

While the Cursor is Open, let me delete the employees whose Occupation is Professional, or Education is Partial High School

USE [SQL Tutorial]
GO
DELETE FROM [EmployeeTable] 
WHERE [Occupation] = N'professional' OR
      [Education] = N'Partial High School'

OUTPUT

If you observe the below screenshot, though the keyset cursor is open, it is terminated after it reaches 3rd record because the 4th record has the Occupation = Professional so the fetch status is returning -2. Try to increase the Time delay to get the correct result.

Let me check the Employee table, whether the delete has happened on the original table or not.

Thank you for Visiting Our Blog