How do the cursors work?

Question

How do the cursors work?

Viewing 12 posts - 16 through 26 (of 26 total)

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

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

Thanks Jeff, Matti and John for correcting me and clearing up my knowledge of Temp Tables/table variables. I'm glad Hector got a sound response.

SQL guy and Houston Magician

Jeff Moden SSC Guru Points: 1003961 More actions · Answer 2

We're all in this together and I'm pullin' for ya" --Red Green

And, thanks for the feedback, Robert... that's what this is all about.

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

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 3

i have done a test and when i created a temp table i watched the table's name appears into tempbd, but then the temp tables always write into disk no matter if them are small??

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Run this:

select * from tempdb..sysobjects

declare @Temp TABLE (ID int)

select * from tempdb..sysobjects

What do you see?

_____________
Code for TallyGenerator

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 5

Run this

select * from tempdb..sysobjects

create table #temp ( a int)

select * from tempdb..sysobjects

what do you see?

i refered to a temporal table and not to var temp table

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Did you actually run it?

Did you notice any difference between @temp and #temp?

_____________
Code for TallyGenerator

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 7

In my sql your query not appears the name of var temp into list and in my query appears the name of temporal table #temp into list

My query :

select * from tempdb..sysobjects

create table #temp ( a int)

select * from tempdb..sysobjects

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Did you look closely?

Run it again:

select * from tempdb..sysobjects

create table @temp ( a int)

select * from tempdb..sysobjects

and tell me how many rows come from 1st select and how many from 2nd one.

Find what is causing the difference.

_____________
Code for TallyGenerator

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 9

well nothing because you uses '@' for the create table , so fixed that i saw the var temp name #7F16D496 , yes i realized that both write to disk , mmm well i imagine that var tables have more chache enables than a temporal table???

Sergiy SSC Guru Points: 110208 More actions · Answer 10

What do you mean by "var tables have more chache enables than a temporal table"?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003961 More actions · Answer 11

Temp tables do NOT always write to disk if they are small... do see Q4of the following URL which I've now posted 3 times in this thread...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

... which says...

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

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

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 12

well but the name of table variable appears like a name of pointer to memory in my tempbd when it is declared, or my server's memory is not avaible or i guess that the structure of the table variable always is copy to disk and only when the memory cache is not avaible it begins to fill or isnt???.