1. Scope of temporary variable is session. For
example if we will execute following query in same tab of sql server it will
execute fine.
CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)
Go
INSERT INTO #temp VALUES(1,'Davis')
SELECT * FROM #temp
But if we will execute
following query in other tab
SELECT * FROM #temp
We will get error
message like: Invalid object name '#temp'.
Note: When we open new
query page or tab in sql server management studio it create a new session.
Scope of table variable
is batch. For example we will execute following sql queries
DECLARE @table TABLE(
ID INT,
Name VARCHAR(50)
)
Go
INSERT INTO @table VALUES(1,'Davis')
SELECT * FROM @table
We get error like: Must
declare the table variable "@table"
Note: Go keyword in sql
server always creates a new batch.
2. Temporary table
is same as normal table with different scope. We can create non
– clustered index on temporary table. For example:
CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)
CREATE NONCLUSTERED INDEX temp_table_index
ON #temp(ID , Name)
We cannot create a
non-clustered index on table variable. For example:
DECLARE @table TABLE(
ID INT,
Name VARCHAR(50)
)
CREATE NONCLUSTERED INDEX temp_table_index
ON @table(ID , Name)
We will get some error message.
Note: If we have large
amount of data and performing quires on it then temporary table
may be better option than table variable since we can increase the performance of
a query by creating indexes on it.
3. There is not any
table lock concept in the table variables. For example it is correct to write:
SELECT * FROM #temp WITH(NOLOCK)
While it is incorrect to
write:
SELECT * FROM @table WITH(NOLOCK)
3. Unlike to
the temporary table, table variables also not
support transaction and logging. For example:
CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)
BEGIN TRANSACTION
INSERT INTO #temp VALUES(1,'Davis')
ROLLBACK
SELECT * FROM #temp
Select query will not
return any rows since it has been rollback while if will perform
same operation on table variable for example:
DECLARE @table AS TABLE(
ID INT,
Name VARCHAR(50)
)
BEGIN TRANSACTION
INSERT INTO @table VALUES(1,'Davis')
ROLLBACK
SELECT * FROM @table
In this case select
query will return one row like
Since
table variables doesn’t support transaction and rows has
not been roll backed.
4. Tables variables in
the stored procedures are pre- compiled while temporary tables are not.
5. We can pass the table
variables as a parameter of stored procedures or functions. For example:
CREATE TYPE InfoType AS TABLE(
ID INT,
Name VARCHAR(50)
)
GO
CREATE PROCEDURE GetInfo(
@table AS InfoType READONLY
)
AS
BEGIN
SELECT * FROM @table
END
Now we will execute the
above stored procedure by passing table variable.
DEClARE @info AS InfoType
INSERT INTO @info VALUES(1,'Davis')
EXECUTE GetInfo @info
Output:
While a temporary table cannot
be passed. For example:
CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)
INSERT INTO #temp VALUES(1,'Davis')
EXECUTE GetInfo #temp
We will get the error
message like: nvarchar is incompatible with InfoType
6. A table value
function can return table variable but it cannot return temporary table.
7. With help of SELECT
INTO we can directly create the temporary table while it is not possible
for temporary variables. For example:
SELECT * INTO #temp
FROM Student
It is not possible in
case of table variables.
SELECT * INTO @table
FROM Student
If we will execute above
query we will get error message like: Incorrect syntax near '@table'
