#temp Table (Temporary Table)
temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.
The data in this #temp table (in fact, the table itself) is visible only to the current scope. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.
Syntax:
— create temporary table
CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )
— populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT AutoID, MyName
FROM myOriginalTable
WHERE AutoID <= 50000
— Drop temporary table
drop table #myTempTable
@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.
Syntax:
DECLARE @myTable TABLE (
AutoID int,
myName char(50) )
INSERT INTO @myTable (AutoID, myName )
SELECT YakID, YakName
FROM myTable
WHERE AutoID <= 50
We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.
Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
Temp tables behave same as normal tables and are bound by transactions.
A simple example shows this difference quite nicely:
BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
select * from #temp
select * from @var
ROLLBACK
select * from @var
if object_id('tempdb..#temp') is null
select '#temp does not exist outside the transaction'
We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.
Best way to Use #Temp table and @Table variable
Here I am going to discuss how often we can use #Temptable and @Temp variable in real T-SQL Code
Whenever we are writing T-SQL Code, we need a table in which to store temporarily data, based on this SQL offer four different table options:-
1) Normal tables
2) Local temporary tables
3) Global temporary tables
4) Table Variables
• Normal tables are that exactly physical tables defined in our database.
• Local Temporary tables are temporary table that are available only to the session that created. This tables are automatically distroyed at the termination of the procedure.
• Gloabal Temporary tables are temporary tables that are avaible to all sessions and all users. They are dropeed automatically when last session using temporary table has completed. Both local temporary tables and global temporary tables are physical are physically created in the tempdb database.
• Temp variables are stored within memory but its set as table. Table variables are partially stored on disk and partially stored in memory. The Access time for table variable can be faster than the temproray table.
The difference between accessing tables and variables cause the internal SQL server processes, Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tabels just as any other database tables. Becasue data reads on temproray table are made a READ lock is placed on the table.
If you are applying a locking process takes its time and consume CPU resources. When reading data from table variable, because table variable partially stored in memory and can not accessed by any other user. In very busy database lack of locking can improve system performance.
On Heavy load system that has good to use temporary tables, the disk array in the TEMPDB database will experience a higher than expected load, becuase all the reads and writes to the temporary tables are done withig the tempdb database. Table variable will perform poorly with large record sets. When many users start using table variable we have to concerned because large Amount of RAM are used.
Table variable should hold no more 2 megs or 3 megs of data (depends on user load system momory)
Does anyone have any clue about this?
Permissions and transaction isolation levels are not an issue here.
— just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN
SELECT 'DROP Temporary table'
DROP TABLE #tempTable
END
IF OBJECT_ID('normalTable') IS NOT NULL
BEGIN
SELECT 'DROP Normal table'
DROP TABLE normalTable
END
GO
— THIS FAILS WITH ERROR MESSAGE: Msg 2714, Level 16, State 1, Line 7 There is already an object named '#tempTable' in the database.
SELECT 'Temporary table'
SELECT * INTO #tempTable FROM [master]..spt_values
SELECT * FROM #tempTable
DROP TABLE #tempTable
SELECT * INTO #tempTable FROM [master]..spt_values
SELECT * FROM #tempTable
DROP TABLE #tempTable
GO
— THIS RUNS OK
SELECT 'Normal table'
SELECT * INTO normalTable FROM [master]..spt_values
SELECT * FROM normalTable
DROP TABLE normalTable
SELECT * INTO normalTable FROM [master]..spt_values
SELECT * FROM normalTable
DROP TABLE normalTable
USE tempdb
SELECT * FROM sysobjects
WHERE name LIKE '%#%'
Whenever you create a local temp table, even though you may call it something like "#tempTable", the entry in temdb..sysobjects is actually called something like "#tempTable_____________________________________________________________
_____________________________________________000000008475"
table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. He also said that SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. The development team member added that you should limit your use of SQL Server 2000 table variables to reasonably small queries and data sets and use temporary tables for larger data sets.
Temporary Tables
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. For example, if I wanted to add a default value to the DateEntered column and create a primary key using the Car_id field I would use the following syntax:
ALTER TABLE dbo.#Cars
ADD
CONSTRAINT [DF_DateEntered] DEFAULT (GETDATE()) FOR [DateEntered],
PRIMARY KEY CLUSTERED
(
[Car_id]
) ON [PRIMARY]
GO
Table Variables
The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:
DECLARE @Cars table (
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int,
DateEntered datetime
)
As you can see the syntax bridges local variable declaration (DECLARE @variable_name variable_data_type) and table creation (column_name, data_type, nullability). As with any other local variable in T-SQL, the table variable must be prefixed with an "@" sign. Unlike temporary or regular table objects, table variables have certain clear limitations.
* 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
Similarities with temporary tables include:
* Instantiated in tempdb
* Clustered indexes can be created on table variables and temporary tables
* Both are logged in the transaction log
* Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.
Usage
Temporary tables are usually preferred over table variables for a few important reasons: they behave more like physical tables in respect to indexing and statistics creation and lifespan. An interesting limitation of table variables comes into play when executing code that involves a table variable. The following two blocks of code both create a table called #Cars and @Cars. A row is then inserted into the table and the table is finally queried for its values.
–Temp Table:
CREATE TABLE dbo.#Cars
(
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int ,
DateEntered datetime
)
INSERT INTO dbo.#Cars (Car_id, ColorCode, ModelName, Code, DateEntered)
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM dbo.#Cars
DROP TABLE dbo.[#Cars]
This returns the following results:
–Table Variable:
DECLARE @Cars TABLE
(
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int ,
DateEntered datetime
)
INSERT INTO @Cars (Car_id, ColorCode, ModelName, Code, DateEntered)
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM @Cars
The results differ, depending upon how you run the code. If you run the entire block of code the following results are returned:
However, you receive an error if you don't execute all the code simultaneously:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Cars"
What is the reason for this behavior? It is quite simple. 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.
Arrao4u 