💎SQL Reloaded: Temporary Tables vs. Table Variables and TempDB

Yesterday, I started to read Ken Henderson’s book, SQL Server 2005 Practical Troubleshooting: The Database Engine, diving directly into tempdb topic (Chapter 9, Tempdb issues). He mentions that metadata are created in system tables when a temporary table is created (see p.415). This means that when a temporary table is created, a record must be created in tempdb’s sys.tables and sys.columns system table, the respective records being deleted when the table is dropped. As I never looked at how the metadata of a temporary table look like, I thought is the case to do something in this direction, and here’s the code created for this purpose:

-- creating the temporary tables 
CREATE TABLE #temp ( 
   id int NOT NULL 
, value nvarchar(50) NOT NULL) 

-- retrieving the metadata 
SELECT t.name table_name 
, s.name column_name 
, t.type  
, t.type_desc  
, t.create_date  
FROM tempdb.sys.tables t 
    JOIN tempdb.sys.columns s 
      ON t.object_id = s.object_id 
WHERE t.name LIKE '%temp%' 

-- dropping the temporary table 
-- DROP TABLE #temp  -- see the 2nd note!

 

Note:
By changing the width of table_name column could be seen that object’s name corresponding to the temporary tables is a combination from table’s name and, according to K. Henderson, the number designating the connection that owns the table.

If the temporary table is stored in tempdb and metadata are stored about it, what’s happening with a temporary table? Here’s the answer:

-- creating the table variable 
DECLARE @temp TABLE( 
  id int NOT NULL  
, value nvarchar(50) NOT NULL) 


-- retrieving the metadata 
SELECT t.name table_name 
, s.name column_name 
, t.type  
, t.type_desc  
, t.create_date  
FROM tempdb.sys.tables t 
     JOIN tempdb.sys.columns s 
      ON t.object_id = s.object_id 
WHERE t.name LIKE '%#%' 
   AND DateDiff(ss, t.create_date, GetDate()) BETWEEN -2 AND 2

As can be seen I had to put a little more effort in order to see a table variable’s metadata. As there is no name that could be used in order to identify the table, as object’s name is stored as a hex number, I had to restrain the list of tables by using the timestamp. Excepting the name, the metadata stored about the two types of tables are identical for the same table definition. Actually their definition is similar with the one of a “standard” table:

-- creating a "standard" table 
CREATE TABLE temp( 
  id int NOT NULL 
, value nvarchar(50) NOT NULL) 

-- retrieving the metadata 
SELECT t.name table_name 
, s.name column_name 
, t.type  
, t.type_desc  
, t.create_date  
FROM sys.tables t 
     JOIN sys.columns s 
      ON t.object_id = s.object_id 
WHERE t.name LIKE '%temp%' 

-- dropping the table 
-- DROP TABLE temp -- see the 2nd note!

Notes: 
(1) For exemplification I used a restrained list of attributes, when comparing the various table’s metadata could be used instead a SELECT * statement. The above examples reflect also the differences in declaring the three types of tables.

(2) Microsoft recommends not to drop the temporary tables explicitly, but let SQL Server handle this cleanup automatically and take thus advantage of the Optimistic Latching Algorithm, which helps prevent contention on TempDB [1].

Last updated: Oct-2024

References:
[1] Haripriya SB (2024) Do NOT drop #temp tables (link)

💎SQL Reloaded: Running a Statement for Each Database (CLR Version)

    I continue the series of posts on the use of the dbo.ExecuteScalarTo  CLR functions created in a previous post, this time, as is mentioned in the title, by running a statement for each database. The old-fashion method of a statement for each database is with the help of undocumented stored procedure sp_MSforeachdb, see for example ReplTalk’s post found recently. Actually the respective example based on the use of DatabaseProperty function has been given only for exemplification, the values available through the DatabaseProperty function could be retrieved directly from the sys.databases system table: 

 -- querying directly the values 
SELECT database_id 
, name DatabaseName 
, Is_Fulltext_enabled IsFulltextEnabled 
, Is_Ansi_Null_Default_on IsAnsiNullDefault 
, Is_Ansi_Nulls_on IsAnsiNullsEnabled 
, Is_Ansi_Warnings_on IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    Here’s the same query rewritten using the DatabaseProperty function:

 -- DatabaseProperty function at work 
SELECT database_id 
, name DatabaseName 
, DATABASEPROPERTY( name ,'IsFulltextEnabled') IsFulltextEnabled 
, DATABASEPROPERTY( name ,'IsAnsiNullDefault') IsAnsiNullDefault  
, DATABASEPROPERTY( name ,'IsAnsiNullsEnabled') IsAnsiNullsEnabled  
, DATABASEPROPERTY( name ,'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    The same query could be rewritten using the dbo.ExecuteScalarToString function:  

 -- DatabaseProperty function within CLR function 
SELECT database_id 
, name DatabaseName 
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsFulltextEnabled'')') IsFulltextEnabled 
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiNullDefault'')') IsAnsiNullDefault  
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiNullsEnabled'')') IsAnsiNullsEnabled  
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiWarningsEnabled'')') IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    The usefulness of the dbo.ExecuteScalarToString is minimal in this case, however it could be considered more “complex” examples. For example retrieving a count of the number of tables, views, assemblies or users existing in a database:

-- running a statement for each database 
SELECT database_id 
, name DatabaseName 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.tables') NumberTables 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.views') NumberViews 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.assemblies') NumberAssemblies 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.sysusers') NumberTables 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

   Now it depends on each developer’s inspiration and on the problem attempting to solve. I find dbo.ExecuteScalarToString CLR function quite useful when attempting to run for each database multiple queries that returns a single value. On the other side sp_MSforeachdb provides more flexibility in what concerns the types of queries run, though specific techniques (e.g. temporary tables or table variables) need to be used in order to retrieve sp_MSforeachdb’s output in a single result-set (see for example the Locally Fragmented Indexes example from MSDN’s article on Uncover Hidden Data To Optimize Application Performance).