Database Reference
In-Depth Information
-- Results depends on how P1 is called
select * from #P2Scope
end
go
create proc dbo.P2
as
begin
create table #P2Scope(ID int)
-- Success: #SessionScope is visible because it's created
-- in the session scope
select * from #SessionScope
-- Success - P1 is called from P2 so table #P2Scope is visible there
exec dbo.P1
-- Success #P2Scope is visible from dynamic SQL called from within P2
exec sp_executesql N'select * from #P2Scope'
end
go
-- Success: #SessionScope is visible because it's created in the session scope
select * from #SessionScope
-- Success
exec dbo.P2
-- Error: Invalid object name '#P2Scope'
exec dbo.P1
The temporary table #SessionScope is created on the connection/session level. This table is visible and
accessible from anywhere within the session. Another temporary table, #P2Scope , is created in the stored procedure
dbo.P2 . This table would be visible in the stored procedure (after it has been created) as well as in the other stored
procedures and dynamic SQL called from dbo.P2 . Finally, as you can see, stored procedure dbo.P1 references both
the #SessionScope and #P2Scope tables. As a result, that stored procedure works just fine when it is called from
dbo.P2 stored procedure, although it would fail when called from anywhere else if the temporary table #P2Scope has
not been created.
You can drop temporary tables using the DROP TABLE statement. Alternatively, SQL Server will drop them when
the session has disconnected, or after finishing the execution of the module in which they were created. In the above
example, the #SessionScope table would be dropped when the session disconnects and #P2Scope would be dropped
after the dbo.P2 stored procedure finishes execution.
Global temporary tables are created with the names starting with ## symbols , and they are visible to all
sessions. They are dropped after the session in which they were created disconnects and when other sessions stop
referencing them.
Neither global nor local temporary tables can have triggers defined nor can they participate in views.
Nonetheless, like regular tables, you can create clustered and non-clustered indexes and define constraints in them.
SQL Server maintains statistics on the indexes defined in the temporary tables in a similar manner to regular
tables. Temporary tables have an additional statistics update threshold of six changes to the statistics column, which
regular tables do not have. A KEEP PLAN query hint lets us prevent a statistics update based on that threshold and
match a regular table's behavior.
 
Search WWH ::




Custom Search