Groups > comp.databases.ms-sqlserver > #857

Re: Cursor Question

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Cursor Question
Date	Fri, 02 Dec 2011 12:35:43 +0100
Organization	Erland Sommarskog
Lines	30
Message-ID	<Xns9FAF80203B027Yazorman@127.0.0.1> (permalink)
References	<jbabbk$g59$1@speranza.aioe.org>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="13122"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+vmSjv/1mCjcCXRc7WarVt"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:CvuAwZGYfgSQ7gOi07U4r063NUo=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:857

Show key headers only | View raw

Travis Crow (noreply@invalid.org) writes:
> If I declare a cursor with a "where clause", is that "where clause" 
> evaluated when I declare the cursor or when I open it?

It depends on the type of cursor. There are four types: DYNAMIC, KEYSET, 
STATIC and FAST_FORWARD.

If you have a dynamic cursor, the query is essentially evaluated each time 
you do FETCH. That is, rows that are added to the table while the cursor is 
running will be visible.

With a static cursor, the result set of the cursor is saved to a temp 
table and the rows are served from this table. I believe this happens at 
OPEN time, but you could easily test to find out.

With a keyset cursor, only the keys are saved to the table, and remaining 
rows are read from the real table at FETCH.

With FAST_FORWARD, I don't even know what happens.

I strongly recommend to stick with STATIC cursors.

-- 
Erland Sommarskog, SQL Server MVP, esquel@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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Cursor Question Travis Crow <noreply@invalid.org> - 2011-12-02 11:04 +0000
  Re: Cursor Question Erland Sommarskog <esquel@sommarskog.se> - 2011-12-02 12:35 +0100
    Re: Cursor Question Travis Crow <noreply@invalid.org> - 2011-12-02 12:03 +0000

csiph-web