SQL Server Insensitive Cursor

What is a SQL Server Insensitive Cursor? 

An insensitive cursor is used in SQL Server when you want to perform cursor work on data without being affected by changes happening in the underlying table while the query is executing.  Insensitive cursors can’t make changes to underlying tables.  They do create a ‘snapshot’ of the existing data in tempdb when the cursor is declared. 

SQL Server INSENSITIVE cursor

DECLARE MyCursor INSENSITIVE CURSOR
FOR SELECT TOP 1000 * FROM Sales

This cursor contains a snapshot of the first 1000 records from the Sales table.  You perform any cursor operations on that data without having to worry about records changing on you during the time the cursor is being used within your query.  This is helpful, for instance, when you have a table that is being updated very frequently but you’d like to catch the data at a particular point in time and perform reporting or calculations without the data drifting during your query giving you a skewed view of your data for a point in time. 

A variation, using the T-SQL extended syntax, is to use the STATIC keyword to create the same cursor as we did above.

STATIC cursor

When declaring the MyCursor cursor like we did above but using the T-SQL extended syntax, we have  a slight change in where the STATIC keyword appears.

DECLARE MyCursor CURSOR STATIC
FOR SELECT TOP 1000 * FROM Sales

NOT INSENSITIVE and DYNAMIC cursors

Leaving the ‘INSENSITIVE’ keyword off of your CURSOR declaration creates a ‘sensitive’ OR DYNAMIC cursor.  This means that any committed changes to the underlying table made while your cursor is being used will be reflected by your cursor when you fetch using the cursor.  You explicitly declare that you want a ‘sensitive’ cursor by using the ‘NOT INSENSITIVE’ syntax.  NOT INSENSITIVE is the ISO syntax while STATIC is the T-SQL extended syntax.  For the official CURSOR syntax, check out the Microsoft documentation  here.

NOT INSENSITIVE cursor

Declaring the cursor in ISO syntax…

DECLARE MyCursor NOT INSENSITIVE CURSOR
FOR SELECT TOP 1000 * FROM Sales

Declaring the same cursor in T-SQL…

DYNAMIC cursor

DECLARE MyCursor CURSOR DYNAMIC
FOR SELECT TOP 1000 * FROM Sales

The insensitive cursor is used in the same scenarios as the STATIC cursor and is useful in certain cases.  Like all cursors,  they should only be used when a non-cursor option isn’t available.