DYNAMIC Archives - ExampleSQL.com

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.

In this post I’m going to describe how to create and use a dynamic column in a temporary table. Let’s get started.

[Warning: dynamic sql is used here so use this tip with the appropriate security considerations]

Lets say you need to create a temporary table in which one of the column’s length must be dynamic.

Example
You’d like the size of column in a temp table to be change based on some condition such as declare the width of a column to be the same size as the length of another column or data (possibly from a different table or concatenations or a million other things).

Naive First attempt…

DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')';
DECLARE @dynamicSQL NVARCHAR(MAX) = 'CREATE TABLE #dynTestTable ( pk int,' + @dynamicColumn + ' )';

-- DEBUG
SELECT @dynamicLength AS dynamicLength,
@dynamicColumn AS dynamicColumn,
@dynamicSQL AS dynamicSQL ;

EXECUTE ( @dynamicSQL ) ;

INSERT INTO #dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;

After executing…

Msg 208, Level 16, State 0, Line 14
Invalid object name '#dynTestTable'.

The reason for this error is because when EXECUTE runs, it literally creates the temp table in a different session and returns back to the current code path with no change to the local environment. Thus, we are unable to do anything on our #dynTestTable.

One viable alternative is to use global tables…

-- be sure to delete the temp table if it exists first
DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')' ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 'CREATE TABLE ##dynTestTable ( pk int,' + @dynamicColumn + ' )' ;

EXECUTE ( @dynamicSQL ) ;

INSERT INTO ##dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;

SELECT *
FROM ##dynTestTable ;

Results…

(2 row(s) affected)
pk          dynamicColumn
----------- -------------
1           abcde
2           bcdef

(2 row(s) affected)

The global temp table will work…so long as nobody else is doing anything with it (like dropping it). If you’re absolutely sure nothing else will be mucking with your global table then this could work. Another, safer, alternative is to declare the temp table first then alter it with dynamic sql. This gives you exclusive access to the table and no other process can meddle it up while you’re working with it.

IF OBJECT_ID('tempdb.dbo.#dynTestTable') is not null
DROP TABLE #dynTestTable;

CREATE TABLE #dynTestTable ( pk INT ) ;

DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')' ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 'ALTER TABLE #dynTestTable ADD ' + @dynamicColumn + ';' ;

-- DEBUG
SELECT @dynamicLength AS dynamicLength,
@dynamicColumn AS dynamicColumn,
@dynamicSQL AS dynamicSQL ;

EXECUTE ( @dynamicSQL ) ;

INSERT INTO #dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;

SELECT *
FROM #dynTestTable ;

Results:

(2 row(s) affected)
pk          dynamicColumn
----------- -------------
1           abcde
2           bcdef

(2 row(s) affected)

There you have it.

[wpfilebase tag=list id=1 pagenav=1 /]

Tag: DYNAMIC

SQL Server Insensitive Cursor