April 27, 2013 at 1:50 pm
hi,
I have created procedure like .
USE [master]
GO
/****** Object: StoredProcedure [dbo]. Script Date: 04/28/2013 01:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].
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
April 28, 2013 at 6:56 am
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
April 29, 2013 at 8:08 am
The object still exist in under tempdb
April 29, 2013 at 8:12 am
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
April 29, 2013 at 7:44 pm
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].
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].
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.
_____________
Code for TallyGenerator
April 30, 2013 at 3:20 am
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
April 30, 2013 at 3:31 am
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:
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply