SQL cursor summary and Performance Analysis (1)

Cursor type

Work on table

Rows retrieve

Scrolling type

SQL statement

Fast?

Read Only?

Client?

Server?

Open cost

Fetche cost

Scroll overhaed

Impact tempdb

Concurrency

Lock

Forward-only

Directly on table

Until fetch

Only fetch next

Fast_forward

Fast

Yes

Yes

Yes

Low (not tempdb)

Low

Low

Low

Static

Snapshot in tempdb

After Open cursor

All scrolling options

Static

Faster

Yes

Yes

Yes

Highest

Lowest (create in tempdb)

Highest

No lock

Keyset-driven

1. Controlled by keyset 2. After Open cursor, create the keyset of row in the tempdb 3. Limit the membership

1. first look at the keyset of rows in tempdb. 2. Navigate to table to retrieve the data.

All scrolling options

Keyset

Slowly

No, all changes

No

Yes

Lower open cost than the static cursor; Higher open cost than forward-only and dynamic cursors

Highest

Lower impact than the static cursor; Higher than forward and dymamic cursor

Higher lock than the static cursor.

Dynamic

Directly on table

Until fetch

All scrolling options, cannot t bsolute

Dynamic

Slowest

No, all changes

No

Yes

Low (not tempdb) than static and keyset cursor

highest lock

cursor type	Update?	lock?	fast?
Read Only	NO	no locks	fastest
optimistic	Yes	no locks	fast
Scroll locks	Yes	U Lock	Slowest