when we create table variable where does the data stores?

Question

when we create table variable where does the data stores?

binu john

SSCommitted

Points: 1987
More actions
June 14, 2005 at 6:02 am

#164020

Hi,
Plse clarify my doubt...
when we create table variable where does the data stores?
I read some where that 'table variable does'nt occupy space in the tempdb as its temporary table counter part.Instead, the table data type uses the sql serevers memory to store data.'
Is it correct??
but I have monitored memory ,cpu and tempdb.
space of tempdb getting full and cpu utalization is high and nothing happens to memory??
Rgds
Binu John

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

It depends on the size of the table (and the memory avalaible). If the table is very small then it's gonna be in memory only, but at some point, it's gonna start writting on the disk.

binu john SSCommitted Points: 1987 More actions · Answer 2

Hi,

But this time I have made the data file size as I mb and run the query

set nocount on

declare @tmptable table

(

login char(50),

hstname char(50)

)

--select * from @tmptable

declare @i int

set @i=1

while @i<=1000000000

begin

insert into @tmptable values('binu','john')

set @i=@i+1

print @i

end

but after printing 11361 it shown the below error.

Could not allocate space for object '#78D4B6B2' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

From that it is clear that physical memory is not utilizing here.

Am I correct??

I have still enough memory that is not utilizing...

Rgds

Binu John

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

June 14, 2005 at 6:58 am

#565268

Obviously, nice thinking about this script.

binu john SSCommitted Points: 1987 More actions · Answer 4

Dear Remi,

But can you clear the things for me.

I read this on 'Sql server 2000 for Experienced DBAs' writern by Brian Knight.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

both #temp tables and table variables may be created in memory, likewise both may be created in tempdb.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

binu john SSCommitted Points: 1987 More actions · Answer 6

but under what circumstances that will create in memory???

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

In order for it to be created, the table has to go in tempdb. The notion of created in memory comes from the fact that very small tables will probabely remain in heap for faster access while much bigger tables with require some disk io to be read.

binu john SSCommitted Points: 1987 More actions · Answer 8

Then what is the advantage of having Table variable compared to temporary table??

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Gives a second option. Less risk of sysobjects deadlocks. Can be faster under certain circumstances. But the fact is that you have to test in your environement to see which performs better with the expected data.

cutespn SSCommitted Points: 1669 More actions · Answer 10

Even the table variables get stored in Temp db, if you wanna check try this script

Use tempdb

select count(*) from sysobjects where type = 'U' -- initial count

go

declare @myvar table (a int)

select count(*) from sysobjects where type = 'U' -- initial count + 1

go

select * from sysobjects where type = 'U' -- initial count again

go

Regards,
Sudheer