Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #856 > unrolled thread

Cursor Question

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#856 — Cursor Question

If I declare a cursor with a "where clause", is that "where clause" 
evaluated when I declare the cursor or when I open it?

I'd assume the latter but I just wanted to make sure.

(in anticipation to a certain question/comment, yes I do try to avoid 
cursors at all times)

-- 
Travis Crow

[toc] | [next] | [standalone]

#857

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

[toc] | [prev] | [next] | [standalone]

#858

On Fri, 02 Dec 2011 12:35:43 +0100, Erland Sommarskog wrote:

> 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.

Thanks. I have tested this and it appears to be at OPEN time.

-- 
Travis Crow

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web