Category: Tables

Temporary Tables/Variables
Temporary tables are created in tempdb database. They function like regular tables where you can perform Data Modification Language (DML) queries. If you create temp table inside stored procedure it gets destroyed once the stored procedure is completed. Scope of the temporary table is important, visible only to the particular user. Temporary tables begin with a pound symbol ‘#’ or ‘@’. Temporary tables can be indexed and DDL constraints can be applied.

There are multiple types of temporary tables:
1. Local temporary tables
2. Global temporary tables
3. Table Variable
4. Tempdb permanent tables
5. Global table variable

1. Local Temporary table
– Local temporary tables are only available in the current executing scope or the current connection to an instance of SQL Server
– Deleted after disconnects from SQL Server instance
– Cannot be shared between multiple users

CREATE TABLE #tmp

2. Global Temporary table
– Available in other sessions and any connection after created
– Deleted when all users that are referencing the table disconnected from the instance of SQL Server
– Can be shared between multiple users
– Once global temporary tables are created it is saved in the tempdb database

CREATE TABLE ##tmp

3. Table Variable
– Only visible to the connection that creates it, stored in RAM and delete after the batch or stored procedure is completed
– Table variable is prefixed with an ‘@’ sign
– Cannot have non-clustered indexes
– No constraints , default or statistics values for table variables

DECLARE @tmp TABLE

4. Tempdb tables
– Are visible to everyone and are deleted when the server is restarted

USE tempdb CREATE TABLE tmp

5. Global table variable

DECLARE @@tmp TABLE

Using sp_executesql
-sp_executesql runs under a different scope, so if you execute and add data to temp table you will not be able to access the temp table

EXECUTE sp_executesql N'SELECT name INTO #test'

You will not be able to run the select statement into #test temp table like:

SELECT * FROM #test

Analysis:
– When you breakup a complex query to multiple statements, it is better to use temp table for optimization reasons.
– Temporary tables are preferred over table variables interms of indexing and statistics and lifespan.
– For table variable entire block of code needs to be run whereas for temporary tables you can run temporary table anytime after it is created in the current session

CREATE TABLE #Temp
(
 id int NOT NULL,
 Name varchar (10)
)

INSERT INTO #Temp (id, Name)
VALUES (1,'Test')

SELECT * FROM #temp

You can run this statements seperately and #temp table will run

DECLARE @Temp TABLE
(
 id int NOT NULL,
 Name varchar (10)                                   
)

INSERT INTO @Temp (id, Name)
VALUES (1,'Test')

SELECT * FROM @temp

You will have to run the entire code together to get the results. The table variable lifespan is only for the duraton of the transaction that it runs.

Table variable is useful when you want to return table result from the user-defined function.
eg

CREATE FUNCTION claimsbyCode (@code int)
RETURNS
   @claimsbyCode TABLE (
    [id] [INT],
    [name] [varchar(20)]
   )
....

Working with Temp tables
Two methods to add data to temp table:
1. INSERT INTO temp table
2. SELECT INTO temp table

INSERT INTO temp table
– table needs to be created first and then it allows data to be inserted.
– will reuse data pages which are created in cache for DML operations
– will truncate the table when temp table is dropped

INSERT INTO #temptable
SELECT * FROM physicaltable

SELECT INTO temp table
– create the new table for you based on your select statement and inserts the resulting row from the query.
– creates new pages for table creation and physically remove them when the table is dropped.

SELECT * 
INTO #temptable
FROM physicaltablename
WHERE statement

In terms of performance, SELECT…INTO is much faster when compared with INSERT INTO

Happy days….