fast_forward cursors are read only but are they insensitive?

Question

Post reply

fast_forward cursors are read only but are they insensitive?

gyerena

Old Hand

Points: 375
More actions
October 2, 2014 at 3:08 pm

#297062

Hello,
I have this doubt. I've found different answers for this question in the web. A fast_forward cursor is read only by definition, meaning the rows can't be updated, but I'm not sure if they are insensitive or not. Do they reflect the changes in the database after the cursor is opened?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61152 More actions · Answer 1

I don't know; wish I could tell you. I'm replying to this post because no one else has.

Here's a great article by Itzek Ben Gan that will get you on the right track:

Sequential to Set-Based.:hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ScottPletcher SSC Guru Points: 101202 More actions · Answer 2

Yes, because the cursor is DYNAMIC, at least based on my reading of BOL:

"

DECLARE CURSOR

...

FORWARD_ONLY

...If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified.

"

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101202 More actions · Answer 3

Code below appears to confirm that. Although I did notice that if you added "TOP (100)", it became a KEYSET cursor, even w/o you explicitly indicating that.

DECLARE csr1 CURSOR FORWARD_ONLY FOR

SELECT * --SELECT TOP (100) * ::= KEYSET cursor rather than DYNAMIC

FROM sys.objects

--OPEN csr1

DECLARE @cursor1 CURSOR

EXEC sp_cursor_list @cursor1 OUTPUT, 3

WHILE 1 = 1

BEGIN

FETCH NEXT FROM @cursor1

IF @@FETCH_STATUS <> 0

BREAK

END --WHILE

DEALLOCATE csr1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

gyerena (10/2/2014)
Hello,
I have this doubt. I've found different answers for this question in the web. A fast_forward cursor is read only by definition, meaning the rows can't be updated, but I'm not sure if they are insensitive or not. Do they reflect the changes in the database after the cursor is opened?

The next question would be, "Why do you ask"? Are you planning on doing something with a cursor? If so, consider posting the problem and let's see if we can find a reasonable way around it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gyerena Old Hand Points: 375 More actions · Answer 5

Thanks for your answer.

It was necessary to iterate through the data because there were several calls to stored procedures already developed (and there were call to other stored procedures inside these). However I saw the article you mentioned and it was useful. I found this article about how to substitute cursors for while cycles when it is necessary to iterate: http://www.codemag.com/Article/060113. This was really useful.

Regarding the original question I must say I have more doubts than before, because of some tests I did. I would post them later.

gyerena Old Hand Points: 375 More actions · Answer 6

Alan.B (10/2/2014)
I don't know; wish I could tell you. I'm replying to this post because no one else has.
Here's a great article by Itzek Ben Gan that will get you on the right track:
Sequential to Set-Based.:hehe:

Thanks for your answer.

It was necessary to iterate through the data because there were several calls to stored procedures already developed (and there were call to other stored procedures inside these). However I saw the article you mentioned and it was useful. I found this article about how to substitute cursors for while cycles when it is necessary to iterate: http://www.codemag.com/Article/060113. This was really useful.

Regarding the original question I must say I have more doubts than before, because of some tests I did. I would post them later.