Difference between table variables and temporary variables

1. Syntax table variables
- declare @temp table ( i int)
Temporary tables
- create #temp (i int)
2. Table variables can not have Non-Clustered Indexes
* You can not create constraints in table variables
* You can not create default values on table variable columns
* Statistics can not be created against table variables



Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign. This is the identifier for SQL Server that it is dealing with a temporary table.

The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):
CREATE TABLE dbo.#Cars
(
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int,
DateEntered datetime
)

Temporary tables act like physical tables in many ways. You can create indexes and statistics on temporary tables. You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys. You can also add and drop columns from temporary tables.



A table variable's lifespan is only for the duration of the transaction that it runs in. If we execute the DECLARE statement first, then attempt to insert records into the @Cars table variable we receive the error because the table variable has passed out of existence. The results are the same if we declare and insert records into @Cars in one transaction and then attempt to query the table. If you notice, we need to execute a DROP TABLE statement against #Cars. This is because the table persists until the session ends or until the table is dropped.
So, it would appear that I don't advocate the use of table variables. That is not true. They serve a very useful purpose in returning results from table value functions. Take for example the following code for creating a user-defined function that returns values from the Customers table in the Northwind database for any customers in a given PostalCode:

CREATE FUNCTION dbo.usp_customersbyPostalCode ( @PostalCode VARCHAR(15) )
RETURNS
@CustomerHitsTab TABLE (
[CustomerID] [nchar] (5),
[ContactName] [nvarchar] (30),
[Phone] [nvarchar] (24),
[Fax] [nvarchar] (24)
)
AS
BEGIN
DECLARE @HitCount INT

INSERT INTO @CustomerHitsTab
SELECT [CustomerID],
[ContactName],
[Phone],
[Fax]
FROM [Northwind].[dbo].[Customers]
WHERE PostalCode = @PostalCode

SELECT @HitCount = COUNT(*) FROM @CustomerHitsTab

IF @HitCount = 0
--No Records Match Criteria
INSERT INTO @CustomerHitsTab (
[CustomerID],
[ContactName],
[Phone],
[Fax] )
VALUES ('','No Companies In Area','','')

RETURN
END
GO

The @CustomerHitsTab table variable is created for the purpose of collecting and returning results of a function to the end user calling the dbo.usp_customersbyPostalCode function.
SELECT * FROM dbo.usp_customersbyPostalCode('1010')

SELECT * FROM dbo.usp_customersbyPostalCode('05033')

An unofficial rule-of-thumb for usage is to use table variables for returning results from user-defined functions that return table values and to use temporary tables for storage and manipulation of temporary data; particularly when dealing with large amounts of data. However, when lesser row counts are involved, and when indexing is not a factor, both table variables and temporary tables perform comparably. It then comes down to preference of the individual responsible for the coding process.

You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:
Normal tables are exactly that, physical tables defined in your database.
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Changes to table variables in SQL Server 2008
Microsoft SQL Server 2008 introduces another important change to table variables. The latest SQL Server edition allows you to create a table variable as an input or output parameter of a stored procedure. An array of information can now be passed to a stored procedure without creating a string or XML block and then having to parse it out after executing the stored procedure. There are some restrictions on this however. The table variable parameter must be declared based on a table data type, and the variable must be read-only. When compared to not having this ability at all, these restrictions are minor inconveniences at best.
In the below sample code, a user-defined table type is created and a stored procedure is then defined using this table type. A local variable is declared also using the table type, data is loaded into the variable and then the array of data is passed from the local variable to the input parameter where the records are used within the stored procedure. Here is an example:
create type tt_example AS TABLE
(spid int)
go
create procedure usp_example
@spids tt_example READONLY
AS
SELECT *
FROM @spids
GO
declare @spids tt_example

insert into @spids
select top 10 spid
from sys.sysprocesses

exec usp_example @spids=@spids
How do the internal workings of SQL Server perform differently between table variables and temporary tables?
The differences between accessing tables and variables cause the internal processes within SQL Server to treat the objects quite differently. Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tables just as they would any other database tables. Because reads to a temporary table are made (including local temporary tables), a read lock is placed on the table.
This locking process takes time and CPU resources. When reading from a table variable – because the table variable is stored partially within memory and cannot be accessed by any other user or process on the system – SQL Server knows locking is not required. In a very busy database, this lack of locking can improve system performance because locks do not have to be taken, escalated and checked for each data access operation.
Limits of temporary tables and table variables
Temporary tables and table variables both have their strengths, but they both have weaknesses too. On a heavy load system that has lots of usage of temporary tables, the disk array servicing the tempdb database will experience a higher than expected load. This happens because all reads and writes to the temporary tables are done within the tempdb database. Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables -- large amounts of RAM are used because all temporary tables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).
Another interesting aspect is that if I rollback the transaction involving the table variable earlier, the data in the table variable is not rolled back.
no DDL is allowed on table variables. So if you have a large rowset which needs to be queried often, you may want to use #table when possible so that you can create appropriate indexes. You can get around this by creating unique constraints when declaring table variable.


Thanks
Amita Vashisht