SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table variable is not automatically dropped in TempDB


Table variable is not automatically dropped in TempDB

Author
Message
dastagiri16
dastagiri16
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 515
hi,
I have created procedure like .

USE [master]
GO
/****** Object: StoredProcedure [dbo].[b] Script Date: 04/28/2013 01:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[b]
as
begin
declare @i table (id int)
select * From tempdb.sys.tables
end

and after i ran the executed my proce

exec b

The result is :
name objectid
#023D5A04 37575172 and so on...

i closed the above connection and after i opened new connection and ran the query like

select * from tempdb.sys.tables

it is showing the below result and table exists in tempdb with the name:

The result is :
name objectid
#023D5A04 37575172 and so on...

Question is:
Why did not it automatically dropped when i ran procedure and even i closed the connection please help on this..

Thanks,
Dastagiri
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87487 Visits: 45272
It is effectively dropped, you can't in any way access the table variable after it goes out of scope. As an optimisation, SQL caches the definitions for temp tables & table variables so there's less work involved in recreating them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


dastagiri16
dastagiri16
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 515
The object still exist in under tempdb
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87487 Visits: 45272
Yes, it's an optimisation, SQL caches the definition and a single data page to reduce the work required to recreate it. The table variable's effectively gone, you cannot access it in any way, there's just a shell cached for future use.

Why is it a concern?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10366 Visits: 11961
I made up a quick test to verify Gila's suggestion.
Very interesting outcome.

Whoever is curios try to run this:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[b]
as
begin
declare @i table (id int, NAME nvarchar(150))
select 'during exec', * From tempdb.sys.tables ORDER BY create_date DESC, name
end

GO

select 'after create', * From tempdb.sys.tables ORDER BY create_date DESC, name
GO
exec b
GO
select 'after exec', * From tempdb.sys.tables ORDER BY create_date DESC, name
go
exec b
GO
select 'after exec again', * From tempdb.sys.tables ORDER BY create_date DESC, name
go
DROP proc [dbo].[b]
GO
select 'after drop', * From tempdb.sys.tables ORDER BY create_date DESC, name
GO
WAITFOR DELAY '00:01:00'

select 'after 1 minute wait', * From tempdb.sys.tables ORDER BY create_date DESC, name
GO



On my desktop (being effectively idle at the time) the table variable disappeared only in the "after 1 minute" resultset.

Very different from the behaviour on SQL2000: object is gone immediately after SP is completed.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87487 Visits: 45272
Temp table caching was added in SQL 2005 to alleviate the contention on the TempDB allocation pages caused by frequent creation of temp tables. You can probably find a few articles on it, not something that's been widely written about though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10366 Visits: 11961
GilaMonster (4/30/2013)
Temp table caching was added in SQL 2005 to alleviate the contention on the TempDB allocation pages caused by frequent creation of temp tables.


I wish that would be a bottleneck in our systems...
Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search