sql server programming: I just read something surprising in my 2005 cert study materials: Sql Server
cannot generate an execution plan for a cursor. Is that really true? I knew
cursors were a bad idea, because they're not set-based, etc., but I didn't

CLM (CLM@discussions.microsoft.com) writes:
[quoted text, click to view]

Of course there must be an execution plan. I don't know what your book
may have in mind.

But there are different forms cursors, and I will have to confess that some
of them, keyset and dynamic, are still very alien to me. A static or
insensitive cursor runs the query ones, and stashes the result into an
area in tempdb. I recommend making all cursors static on the principle of
least surprise.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

/*
on cursors:
Say you had 5 million customer OLTP environment that was operational 24/7,
you had just received a data clensing update for postcode/address
correction. How would you update your customer database with the new
addresses ?

Although a set based approach would work, You might be looking for a new job
before the query finished.

on execution plans:
Try running the following code in management studio / query analyser with
execution plans turned on and take a look:
*/

CREATE TABLE Curs1 (
pk int PRIMARY KEY,
data int
)

CREATE TABLE Curs2 (
pk int PRIMARY KEY,
data int
)

INSERT Curs1 VALUES (1, 10)
INSERT Curs1 VALUES (2, 20)
INSERT Curs1 VALUES (3, 30)

INSERT Curs2 VALUES (1, 100)
INSERT Curs2 VALUES (2, 200)
INSERT Curs2 VALUES (3, 300)

GO
/* STATIC snapshots all the results into tempdb, can be good for complex
queries where you will consume the whole result set, Although this method
hits the server the processing load up front. This will not see any changes
to underlying data during processing */

DECLARE CSTATIC CURSOR STATIC FOR
SELECT curs1.pk, curs1.data, curs2.pk, curs2.data
FROM curs1
JOIN curs2 on curs2.pk=curs1.pk
OPEN CSTATIC
FETCH CSTATIC
FETCH CSTATIC
CLOSE CSTATIC
DEALLOCATE CSTATIC

GO
/* DYNAMIC re-executes the query (similar to Top 1) on each fetch and stores
data in tempdb to allow for a subsequent update. This can be useful for
places where you do not consume the entire result set e.g. pagination. This
can see all changes to underlying data during processing.*/

DECLARE CDYNAMIC CURSOR DYNAMIC FOR
SELECT curs1.pk, curs1.data, curs2.pk, curs2.data
FROM curs1
JOIN curs2 on curs2.pk=curs1.pk
OPEN CDYNAMIC
FETCH CDYNAMIC
FETCH CDYNAMIC
CLOSE CDYNAMIC
DEALLOCATE CDYNAMIC

GO
/* DYNAMIC READ_ONLY as above but without storing the data necessary for
updating.*/

DECLARE CRODYNAMIC CURSOR DYNAMIC READ_ONLY FOR
SELECT curs1.pk, curs1.data, curs2.pk, curs2.data
FROM curs1
JOIN curs2 on curs2.pk=curs1.pk
OPEN CRODYNAMIC
FETCH CRODYNAMIC
FETCH CRODYNAMIC
CLOSE CRODYNAMIC
DEALLOCATE CRODYNAMIC

GO
/* KEYSET spools the results into tempdb like STATIC but only the primary
keys of each referenced table are stored rather than the complete result
set. Looks up the data in each table on each fetch and stores data in tempdb
to allow for a subsequent update. Can be good if your query is very complex
with many columns. This can see updates (and deletes but not updates) to the
underlying data during processing.*/

DECLARE CKEYSET CURSOR KEYSET FOR
SELECT curs1.pk, curs1.data, curs2.pk, curs2.data
FROM curs1
JOIN curs2 on curs2.pk=curs1.pk
OPEN CKEYSET
FETCH CKEYSET
FETCH CKEYSET
CLOSE CKEYSET
DEALLOCATE CKEYSET

GO
/* KEYSET READ_ONLY as above but without storing the data necessary for
updating.*/

DECLARE CROKEYSET CURSOR KEYSET READ_ONLY FOR
SELECT curs1.pk, curs1.data, curs2.pk, curs2.data
FROM curs1
JOIN curs2 on curs2.pk=curs1.pk
OPEN CROKEYSET
FETCH CROKEYSET
FETCH CROKEYSET
CLOSE CROKEYSET
DEALLOCATE CROKEYSET

GO
/* FAST_FORWARD stripped down dynamic read_only cursor with minimal resource
requirements.*/

DECLARE CFASTFORWARD CURSOR FAST_FORWARD FOR
SELECT curs1.pk, curs1.data, curs2.pk, curs2.data
FROM curs1
JOIN curs2 on curs2.pk=curs1.pk
OPEN CFASTFORWARD
FETCH CFASTFORWARD
FETCH CFASTFORWARD
CLOSE CFASTFORWARD
DEALLOCATE CFASTFORWARD

-- Mr Tea

[quoted text, click to view]

CLM,

I think you are looking at this the wrong way.

Cursors are not 'bad' at all... at least no less useful than any tool
used for the job in which they were intended. Indexes are 'bad' if you
have too many. Normalisation is 'bad' if you over do it. Cursors have an
important place in creating script to loop and process where it is just
way too complex to write everything in a single statement. It is also
good for conditional processing and developing things like running
balances. But you can misuse them easily. True.

But as for no execution plans...well it makes sense because it isn't a
single query as such. You are fetching individual rows one at a time and
there for an execution plan doesn't have the same meaning or need. I
would be surprised if the original statement didn't get some kind of
plan but from there... well... make sure your query is good !

Cheers,

Geoff



[quoted text, click to view]