Difference declaring cursor like variable with @ or not

Question

I got this question that I can't find an answer:

In SQL Server, what's the difference between

DECLARE CRS_RQS INSENSITIVE CURSOR FOR
    SELECT pRqsMtr FROM dtRqsMtr
    WHERE pRqs = @pRqs
OPEN CRS_RQS
FETCH NEXT FROM CRS_RQS INTO @pRqsMtr
WHILE (@@Fetch_Status = 0) AND (@@Error = 0)
BEGIN

    -- do some stuff...
    FETCH NEXT FROM CRS_RQS INTO @pRqsMtr
END 
CLOSE CRS_RQS
DEALLOCATE CRS_RQS

and this?

DECLARE @CRS_RQS CURSOR
SET @CRS_RQS = VOID
SET @CRS_RQS = CURSOR FOR
    SELECT pRqsMtr FROM dtRqsMtr
    WHERE pRqs = @pRqs
OPEN @CRS_RQS
FETCH NEXT FROM @CRS_RQS INTO @pRqsMtr
WHILE (@@Fetch_Status = 0) AND (@@Error = 0)
BEGIN

    -- do some stuff...
    FETCH NEXT FROM @CRS_RQS INTO @pRqsMtr
END 
CLOSE @CRS_RQS
DEALLOCATE @CRS_RQS

and.. what's the best of the 2 above?

thanks in advance

Answer 1

If you are sure you actually need a cursor (it is the wrong choice more often than not), best is @CRS_RQS because:

• it automatically leaves out some of the heavier default options from a "normal" cursor; and,
• you don't have to close/declare; it does that automatically.

See this post from Itzik Ben-Gan on why he's switched to local cursor variables:

• Overlooked T-SQL Gems

And see some of my performance research on the best options to use for a "real" cursor:

• What impact can different cursor options have?
• Follow-up on cursor options

The most salient point is to use LOCAL FAST_FORWARD unless you know you need different options (all the options are described in the documentation).