cursor types difference

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
> This sounds suspiciously like homework.

Why on earth would anyone give homework about the cursor types in SQL
Server? In such case that teacher should be flogged officially!

> Did you bother to look at the topic DECLARE CURSOR in Books Online?

If he did, and did ont get it, I am not going to blame him. I'm still
not sure that I have after all these years.

I will try to explain to Somu, the best as I can.

There are six cursor types: SCROLL, INSENSITIVE, STATIC, FAST_FORWARD,
KEYSET and DYNAMIC. Of hese the first two are put before the CURSOR
keyword, as they are ANSI-compliant, whereas the other are proprietary.

The easiest types to understand are STATIC and INSENSITIVE. As far as they
are the same. And while INSENSITIVE is ANSI-compliant, you will prefer
to use STATIC, as this permits you to use the LOCAL keyword. This causes
the cursor to goes away when the scope it was created in exits.

In a STATIC/INSENSITIVE cursor, the query is run when you DECLARE the
cursor, and the result is put in an area in tempdb. When you fetch rows,
you fetch from this area. This is very nice, because you know exactly
which rows you work with, and nothing can happen when you run the cursor.

In a DYNAMIC cursor, the query in the DECLARE statement is run every
time you say FETCH. As you can imagine, this can be very expensive if the
query is poorly written. And if the query uses indexes, iterating
over all rows, is a lot slower with a dynamic cursor than with a static
cursor, despite the latter having the overhead of copying the rows.
Beware that DYNAMIC is the default type!

In a KEYSET cursor, the query is run at DECLARE as with a static cursor,
but only the *keys* are stored in tempdb. (Please don't ask me, how those
keys are identified!). Then remaining rows are queried as you loop through
the cursor.

I will have to leave SCROLL and FAST_FORWARD to someone else.

In the very most cases, a static cursor is what you want. I recently
had a case where I realised that a dynamic cursor gave me better
performance. This was because of potentially hundreds of thousands
of rows, I would typically only need to read five. But, no, I did not
use a dynamic cursor, I preferred roll the loop on my own, so that I
had full control of what is going on.




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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: cursor types difference 

>> Did you bother to look at the topic DECLARE CURSOR in Books Online?
>
> If he did, and did ont get it, I am not going to blame him. I'm still
> not sure that I have after all these years.

Well if it is just a homework assignment (and yes this type of question is a
homework assignment more often than you might suspect), it is probably
sufficient to regurgitate the definitions that are in Books Online as
opposed to thoroughly understanding if and when one should use each type.
 >> Stay informed about: cursor types difference 

> regurgitate

Spot on!


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: cursor types difference 

On Dec 30, 6:02 am, ML wrote:
> > regurgitate
>
> Spot on!
>
> ML
>
> ---
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/

But in most cases the best type of cursor is a foreach loop in C#.
 >> Stay informed about: cursor types difference 

In a STATIC/INSENSITIVE cursor, the query is run when you DECLARE the
cursor, and the result is put in an area in tempdb.

I would like to modify the above statement like this.


In a STATIC/INSENSITIVE cursor, the query is run when the cursor is opened(and not at the time of declare cursor), and the result is put in an area in tempdb.

Check the following code. I have inserted one more row in temp table after declaring the cursor. If the query executed at the time of declaring cursor @@cursor_rows should return 1 but it returns 2.

====================================================

CREATE TABLE #MyTable
(
MyPK int NOT NULL PRIMARY KEY,
MyData int NOT NULL
)

INSERT INTO #MyTable VALUES(1, 1)

DECLARE InsensitiveCursor
INSENSITIVE CURSOR FOR
SELECT MyPk, MyData
FROM #MyTable
FOR READ ONLY

INSERT INTO #MyTable VALUES(2, 2)

OPEN InsensitiveCursor

select @@CURSOR_ROWS

CLOSE InsensitiveCursor
DEALLOCATE InsensitiveCursor

DROP TABLE #MyTable


> On Sunday, December 28, 2008 2:17 PM somu wrote:

> please give full details of each and every types of cursors . espesiaaly about keyset cursors
>
> From http://www.developmentnow.com/g/113_2006_10_0_2_0/sql-server-programming.htm
>
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com/g/


>> On Sunday, December 28, 2008 2:33 PM Aaron Bertrand [SQL Server MVP] wrote:

>> This sounds suspiciously like homework. Did you bother to look at the topic
>> DECLARE CURSOR in Books Online?
>>
>> http://msdn.microsoft.com/en-us/library/ms180169(SQL.90).aspx
>>
>>
>>
>>
>>
>>
>> On 12/28/08 2:17 PM, in article
>> a8f187e4-2a45-407a-9803-da90f95d8bbe DeleteThis @developmentnow.com, "somu"
>> wrote:


>>> On Sunday, December 28, 2008 5:37 PM Erland Sommarskog wrote:

>>> Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
>>>
>>> Why on earth would anyone give homework about the cursor types in SQL
>>> Server? In such case that teacher should be flogged officially!
>>>
>>>
>>> If he did, and did ont get it, I am not going to blame him. I'm still
>>> not sure that I have after all these years.
>>>
>>> I will try to explain to Somu, the best as I can.
>>>
>>> There are six cursor types: SCROLL, INSENSITIVE, STATIC, FAST_FORWARD,
>>> KEYSET and DYNAMIC. Of hese the first two are put before the CURSOR
>>> keyword, as they are ANSI-compliant, whereas the other are proprietary.
>>>
>>> The easiest types to understand are STATIC and INSENSITIVE. As far as they
>>> are the same. And while INSENSITIVE is ANSI-compliant, you will prefer
>>> to use STATIC, as this permits you to use the LOCAL keyword. This causes
>>> the cursor to goes away when the scope it was created in exits.
>>>
>>> In a STATIC/INSENSITIVE cursor, the query is run when you DECLARE the
>>> cursor, and the result is put in an area in tempdb. When you fetch rows,
>>> you fetch from this area. This is very nice, because you know exactly
>>> which rows you work with, and nothing can happen when you run the cursor.
>>>
>>> In a DYNAMIC cursor, the query in the DECLARE statement is run every
>>> time you say FETCH. As you can imagine, this can be very expensive if the
>>> query is poorly written. And if the query uses indexes, iterating
>>> over all rows, is a lot slower with a dynamic cursor than with a static
>>> cursor, despite the latter having the overhead of copying the rows.
>>> Beware that DYNAMIC is the default type!
>>>
>>> In a KEYSET cursor, the query is run at DECLARE as with a static cursor,
>>> but only the *keys* are stored in tempdb. (Please don't ask me, how those
>>> keys are identified!). Then remaining rows are queried as you loop through
>>> the cursor.
>>>
>>> I will have to leave SCROLL and FAST_FORWARD to someone else.
>>>
>>> In the very most cases, a static cursor is what you want. I recently
>>> had a case where I realised that a dynamic cursor gave me better
>>> performance. This was because of potentially hundreds of thousands
>>> of rows, I would typically only need to read five. But, no, I did not
>>> use a dynamic cursor, I preferred roll the loop on my own, so that I
>>> had full control of what is going on.
>>>
>>>
>>>
>>>
>>> --
>>> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>>>
>>> Links for SQL Server Books Online:
>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


>>>> On Sunday, December 28, 2008 6:43 PM Aaron Bertrand [SQL Server MVP] wrote:

>>>> Well if it is just a homework assignment (and yes this type of question is a
>>>> homework assignment more often than you might suspect), it is probably
>>>> sufficient to regurgitate the definitions that are in Books Online as
>>>> opposed to thoroughly understanding if and when one should use each type.


>>>>> On Tuesday, December 30, 2008 7:02 AM M wrote:

>>>>> Spot on!
>>>>>
>>>>>
>>>>> ML
>>>>>
>>>>> ---
>>>>> Matija Lah, SQL Server MVP
>>>>> http://milambda.blogspot.com/


>>>>> Submitted via EggHeadCafe
>>>>> Silverlight 4 Coin-Flip Decision Maker based on Mersenne Twister
>>>>> http://www.eggheadcafe.com/tutorials/aspnet/fabf1bbb-9568-4ff6-859b-76...c02f13f
 >> Stay informed about: cursor types difference