SQL Reference - INSENSITIVE

INSENSITIVE

INSENSITIVE is a keyword used in SQL databases to define a cursor that makes a temporary copy of the data selected, allowing it to retain the same select result set even if the underlying data is modified. It therefore ensures a stable set of results for all fetches of the cursor, regardless of changes being made to rows by the database or by other users.

Example

DECLARE cur1 CURSOR
INSENSITIVE FOR
SELECT customerName
FROM Customers;

Output

+------------------+
| customerName     |
+------------------+
| Atelier graphique|
| Signal Gift Stores|
| Australian Collectors, Co.|
| ...              |
+------------------+

Explanation

In this example, the keyword INSENSITIVE is used while declaring a cursor. It is used to create a temporary copy of the data retrieved by the cursor so that changes made to the database data cannot affect the cursor results. The query within the cursor is fetching the customerName from the Customers table.

Example

CREATE PROCEDURE GetOrderSummary AS
BEGIN
DECLARE @Order_Cursor INSENSITIVE CURSOR FOR
SELECT OrderID, CustomerID, OrderDate
FROM Orders

OPEN @Order_Cursor

DECLARE @OrderID int, @CustomerID varchar(10), @OrderDate datetime
FETCH NEXT FROM @Order_Cursor INTO @OrderID, @CustomerID, @OrderDate
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Order ID: ' + CAST(@OrderID AS VARCHAR)
PRINT 'Customer ID: ' + @CustomerID
PRINT 'Order Date: ' + CONVERT(VARCHAR, @OrderDate, 101)
FETCH NEXT FROM @Order_Cursor INTO @OrderID, @CustomerID, @OrderDate
END

CLOSE @Order_Cursor
DEALLOCATE @Order_Cursor
END

Output

Order ID: 10248
Customer ID: VINET
Order Date: 07/04/1996

Order ID: 10249
Customer ID: TOMSP
Order Date: 07/05/1996
...

Explanation

Here, an INSENSITIVE cursor is utilized to retrieve every order from the Orders table. The data is fetched from the cursor using a WHILE loop. The key aspect is that this INSENSITIVE cursor displays the data as it was when the cursor opened, meaning modifications to the table during the cursor’s lifetime are not reflected in the data presented by the cursor.