"Go" static and dynamic cursors

When a static cursor is opened, the dataset is stored in tempdb, so the data displayed is consistent with the data set when the cursor is opened, and updates to the database are not displayed in the cursor after the cursor is opened.

dynamic cursors reflect changes to the database when they are opened. All UPDATE, INSERT, and DELETE operations are displayed in the result set of the cursor, and the row data values, order, and members of the result set change each time the fetch occurs.

If you do not specify the static keyword when you define a cursor, it is dynamic by default.

A dynamic cursor is opened faster than a static cursor. When you open a static cursor, you must generate an internal temporary worksheet, which is not required by a dynamic cursor.

In joins, a static cursor may be faster than a dynamic cursor. Because dynamic cursors react to changes made to the rows of data in the result set as they scroll, it consumes resources to detect changes to the base table, so the processing of cursors that do not need to reflect the updated cursor of the base table should be defined as static cursors for complex queries.

If you are making an absolute fetch, you must use a keyset-driven or static cursor.

When using dynamic cursors, you should be aware of problems such as the dead loop caused by updates to the row data, as shown in the following table:

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo]. [Test] (

[ID] [int] not NULL,

CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED ([ID] ASC) on [PRIMARY]

GO

INSERT into Test VALUES (1)

GO

DECLARE C$cursor Cursor

For

SELECT ID from Test Where ID < 10

DECLARE @id INT

OPEN C$cursor

Fetch C$cursor into @id

While @ @FETCH_STATUS = 0

BEGIN

UPDATE Test SET id = id + 1 WHERE id = @id

Fetch C$cursor into @id

END

CLOSE C$cursor

Deallocate c$cursor

After execution, the values in the table become 10, representing the update line multiple times. If you change the definition of the cursor to the following:

DECLARE c$cursor Cursor STATIC

For

SELECT ID from Test Where ID < 10

The execution result is 2

"Go" static and dynamic cursors