need to know the detail about cursor.

30 Dec 2005 10:51 PM

Skywalker

Hi,

I have a Table1( C1,C2,C3)

DECLARE T1_cursor CURSOR FOR
SELECT C1,C2 FROM Table1 WHERE C3=1

Tabel1 has multi-millines records. Say if the snap-shot of the T1_cursor has
10000 records when the cursor start running (Fetch), I add more records to
Table1 which has C3=1 while the T1_cursor running.
My question is if the count of T1_cursor will auto change base on my adding
in , or it only lock down the snap-shot at the beginning which is 10000
records only?

Thanks,

Skywalker

30 Dec 2005 11:09 PM

--CELKO--

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.

Look up isolation levels in SQL. Generally speaking, you will lock
down the table at t the highest level you can get while using a CURSOR
to avoid such problems. Of course, you will make everyone else
miserable and your own code will run like glue. Good SQL do not use
CURSORs more than a few times in their entire career.

31 Dec 2005 2:38 AM

Andrew J. Kelly

That depends on how you declare the cursor. You might want to read up in
BooksOnLine under DECLARE CURSOR and the different types. But I agree with
Joe in that you should be looking for a SET based approach first.

--
Andrew J. Kelly SQL MVP

Show quote

"Skywalker" <steven***@yahoo.com> wrote in message
news:eKw6HOZDGHA.4028@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have a Table1( C1,C2,C3)
>
> DECLARE T1_cursor CURSOR FOR
> SELECT C1,C2 FROM Table1 WHERE C3=1
>
> Tabel1 has multi-millines records. Say if the snap-shot of the T1_cursor
> has 10000 records when the cursor start running (Fetch), I add more
> records to Table1 which has C3=1 while the T1_cursor running.
> My question is if the count of T1_cursor will auto change base on my
> adding in , or it only lock down the snap-shot at the beginning which is
> 10000 records only?
>
> Thanks,
>
> Skywalker
>

31 Dec 2005 11:20 AM

David Portas

Skywalker wrote:
Show quote

> Hi,
>
> I have a Table1( C1,C2,C3)
>
> DECLARE T1_cursor CURSOR FOR
> SELECT C1,C2 FROM Table1 WHERE C3=1
>
> Tabel1 has multi-millines records. Say if the snap-shot of the T1_cursor has
> 10000 records when the cursor start running (Fetch), I add more records to
> Table1 which has C3=1 while the T1_cursor running.
> My question is if the count of T1_cursor will auto change base on my adding
> in , or it only lock down the snap-shot at the beginning which is 10000
> records only?
>
> Thanks,
>
> Skywalker

A cursor over 10000 rows is unlikely to be a good idea. Are you sure
there isn't a set-based method of doing this?

--
David Portas
SQL Server MVP
--

1 Jan 2006 12:17 AM

Erland Sommarskog

Skywalker (steven***@yahoo.com) writes:

> I have a Table1( C1,C2,C3)
>
> DECLARE T1_cursor CURSOR FOR
> SELECT C1,C2 FROM Table1 WHERE C3=1
>
> Tabel1 has multi-millines records. Say if the snap-shot of the T1_cursor
> has 10000 records when the cursor start running (Fetch), I add more
> records to Table1 which has C3=1 while the T1_cursor running. My
> question is if the count of T1_cursor will auto change base on my adding
> in , or it only lock down the snap-shot at the beginning which is 10000
> records only?

Depends on the cursor type. The default cursor type is keyset, and is
likely to be affected.

I always create my cursors as INSENSITIVE, this means that the result
set for the cursor is fixed when I open the cursor. A STATIC probably
has the same effect. I don't know about FAST_FORWARD.

Generally, a cursor with 10000 rows is not aimed for good performance.

--
Erland Sommarskog, SQL Server MVP, esq***@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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx