Temp Table Scope Works In Your Favor
Temporary tables are a useful feature in Microsoft SQL Server. You may even be using them without even knowing it. SQL Server will often break down queries into steps, using virtual temp tables as intermediate steps to resolve complicated queries.
If you use persisted temp tables by defining them like a regular table preceded with # or ## the table has interesting properties of scope. If you are connected to the AdventureWorks database you would create a permanent table with a syntax like
CREATE TABLE MyTable (SomeKey INT NOT NULL)
If instead you used the syntax
CREATE TABLE #MyTable (SomeKey INT NOT NULL)
Then the table would instead be created in TempDB. You can explicitly drop the table. If you don’t, when your connection to the database is closed, the temp table is dropped.
One interesting behavior of this table is the scope. If you call a stored procedure in the same connection having created the #temp table, then that table will be available for access. This is one technique, long known to SQL Server developers, that is useful for sharing data across stored procedures.
Do you find temp tables useful? How about using in memory temp tables in the form of a table value variable? Share your thoughts below or drop me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
External Table as a Data Loading Tool
The external tables feature can be used as a data loading tool – a good alternative to SQL Loader.
Featured White Paper(s)
Achieve an astounding return on investment with Toad® for Oracle
read more)
Featured Script
Snipped to convert hex to INT
This code snippet converts from Hex to INT…. (read more)