Manpreet Singh says
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
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).
Both temporary tables and table variables can be extremely useful tools in developers' and administrators' arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.
CREATE table #T (s varchar(128))
DECLARE @T table (s varchar(128))
INSERT into #T select 'old value #'
INSERT into @T select 'old value @'
BEGIN transaction
UPDATE #T set s='new value #'
UPDATE @T set s='new value @'
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s
---------------
old value #
s
---------------
new value @
After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.
The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table-variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
Finally, table-variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in INSERT/EXEC statements.
But let's compare both in terms of performance.
At first, we prepare a test table with 1 million records
CREATE table NUM (n int primary key, s varchar(128))
GO
SET nocount on
DECLARE @n int
SET @n=1000000
WHILE @n>0 begin
INSERT into NUM
SELECT @n,'Value: '+convert(varchar,@n)
SET @n=@n-1
END
GO
Now we prepare our test procedure T1:
CREATE procedure T1
@total int
AS
CREATE table #T (n int, s varchar(128))
INSERT into #T select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from #T
WHERE #T.n=NUM.n)
GO
Called with a parameter, which we will vary from 10, 100, 1000, 10'000, 100'000 up to 1'000'000, it copies the given number of records into a temporary table (with some exceptions, it skips records where n is divisible by 100), and then finds a max(s) of such missing records. Of course, the more records we give, the longer the execution is.
To measure the execution time precisely, I use the code:
DECLARE @t1 datetime, @n int
SET @t1=getdate()
SET @n=100 – (**)
WHILE @n>0 begin
EXEC T1 1000 – (*)
SET @n=@n-1 end
SELECT datediff(ms,@t1,getdate())
GO
(*) is a parameter to our procedure, it is varied from 10 to 1'000'000 (**) if an execution time is too short, I repeat the same loop 10 or 100 times. I run the code several times to get a result of a 'warm' execution.
The results can be found in Table 1 (see below).
Now let's try to improve our stored procedure by adding a primary key to the temporary table:
CREATE procedure T2
@total int
AS
CREATE table #T (n int primary key, s varchar(128))
INSERT into #T select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from #T
WHERE #T.n=NUM.n)
GO
Then, let's create a third one. With a clustered index, it works much better. But let's create the index AFTER we insert data into the temporary table – usually, it is better:
CREATE procedure T3
@total int
AS
CREATE table #T (n int, s varchar(128))
INSERT into #T select n,s from NUM
WHERE n%100>0 and n<=@total
CREATE clustered index Tind on #T (n)
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from #T
WHERE #T.n=NUM.n)
GO
Surprise! It not only takes longer for the big amounts of data; merely adding 10 records take an additional 13 milliseconds. The problem is that 'create index' statements force SQL Server to recompile stored procedures, and slows down the execution significantly.
Now let's try the same using table-variables:
CREARE procedure V1
@total int
AS
DECLARE @V table (n int, s varchar(128))
INSERT into @V select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from @V V
WHERE V.n=NUM.n)
GO
To our surprise, this version is not significantly faster than the version with the temporary table. This is a result of a special optimization SQL Server has for the create table #T statements in the very beginning of a stored procedure. For the whole range of values, V1 works better or the same as T1.
Now let's try the same with a primary key:
CREATE procedure V2
@total int
AS
DECLARE @V table (n int primary key, s varchar(128))
INSERT into @V select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from @V V
WHEREre V.n=NUM.n)
GO
The result is much better, but T2 outruns this version.
Table 1, using SQL Server 2000, time in ms
Records T1 T2 T3 V1 V2
10 0.7 1 13.5 0.6 0.8
100 1.2 1.7 14.2 1.2 1.3
1000 7.1 5.5 27 7 5.3
10000 72 57 82 71 48
100000 883 480 580 840 510
1000000 45056 6090 15220 20240 12010
But the real shock is when you try the same on SQL Server 2005:
Table 2
N T1 T2 T3 V1 V2
10 0.5 0.5 5.3 0.2 0.2
100 2 1.2 6.4 61.8 2.5
1000 9.3 8.5 13.5 168 140
10000 67.4 79.2 71.3 17133 13910
100000 700 794 659 Too long!
Too long!
1000000 10556 8673 6440 Too long!
Too long!
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
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).
Both temporary tables and table variables can be extremely useful tools in developers' and administrators' arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.
CREATE table #T (s varchar(128))
DECLARE @T table (s varchar(128))
INSERT into #T select 'old value #'
INSERT into @T select 'old value @'
BEGIN transaction
UPDATE #T set s='new value #'
UPDATE @T set s='new value @'
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s
---------------
old value #
s
---------------
new value @
After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.
The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table-variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
Finally, table-variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in INSERT/EXEC statements.
But let's compare both in terms of performance.
At first, we prepare a test table with 1 million records
CREATE table NUM (n int primary key, s varchar(128))
GO
SET nocount on
DECLARE @n int
SET @n=1000000
WHILE @n>0 begin
INSERT into NUM
SELECT @n,'Value: '+convert(varchar,@n)
SET @n=@n-1
END
GO
Now we prepare our test procedure T1:
CREATE procedure T1
@total int
AS
CREATE table #T (n int, s varchar(128))
INSERT into #T select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from #T
WHERE #T.n=NUM.n)
GO
Called with a parameter, which we will vary from 10, 100, 1000, 10'000, 100'000 up to 1'000'000, it copies the given number of records into a temporary table (with some exceptions, it skips records where n is divisible by 100), and then finds a max(s) of such missing records. Of course, the more records we give, the longer the execution is.
To measure the execution time precisely, I use the code:
DECLARE @t1 datetime, @n int
SET @t1=getdate()
SET @n=100 – (**)
WHILE @n>0 begin
EXEC T1 1000 – (*)
SET @n=@n-1 end
SELECT datediff(ms,@t1,getdate())
GO
(*) is a parameter to our procedure, it is varied from 10 to 1'000'000 (**) if an execution time is too short, I repeat the same loop 10 or 100 times. I run the code several times to get a result of a 'warm' execution.
The results can be found in Table 1 (see below).
Now let's try to improve our stored procedure by adding a primary key to the temporary table:
CREATE procedure T2
@total int
AS
CREATE table #T (n int primary key, s varchar(128))
INSERT into #T select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from #T
WHERE #T.n=NUM.n)
GO
Then, let's create a third one. With a clustered index, it works much better. But let's create the index AFTER we insert data into the temporary table – usually, it is better:
CREATE procedure T3
@total int
AS
CREATE table #T (n int, s varchar(128))
INSERT into #T select n,s from NUM
WHERE n%100>0 and n<=@total
CREATE clustered index Tind on #T (n)
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from #T
WHERE #T.n=NUM.n)
GO
Surprise! It not only takes longer for the big amounts of data; merely adding 10 records take an additional 13 milliseconds. The problem is that 'create index' statements force SQL Server to recompile stored procedures, and slows down the execution significantly.
Now let's try the same using table-variables:
CREARE procedure V1
@total int
AS
DECLARE @V table (n int, s varchar(128))
INSERT into @V select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from @V V
WHERE V.n=NUM.n)
GO
To our surprise, this version is not significantly faster than the version with the temporary table. This is a result of a special optimization SQL Server has for the create table #T statements in the very beginning of a stored procedure. For the whole range of values, V1 works better or the same as T1.
Now let's try the same with a primary key:
CREATE procedure V2
@total int
AS
DECLARE @V table (n int primary key, s varchar(128))
INSERT into @V select n,s from NUM
WHERE n%100>0 and n<=@total
DECLARE @res varchar(128)
SELECT @res=max(s) from NUM
WHERE n<=@total and
NOT exists(select * from @V V
WHEREre V.n=NUM.n)
GO
The result is much better, but T2 outruns this version.
Table 1, using SQL Server 2000, time in ms
Records T1 T2 T3 V1 V2
10 0.7 1 13.5 0.6 0.8
100 1.2 1.7 14.2 1.2 1.3
1000 7.1 5.5 27 7 5.3
10000 72 57 82 71 48
100000 883 480 580 840 510
1000000 45056 6090 15220 20240 12010
But the real shock is when you try the same on SQL Server 2005:
Table 2
N T1 T2 T3 V1 V2
10 0.5 0.5 5.3 0.2 0.2
100 2 1.2 6.4 61.8 2.5
1000 9.3 8.5 13.5 168 140
10000 67.4 79.2 71.3 17133 13910
100000 700 794 659 Too long!
Too long!
1000000 10556 8673 6440 Too long!
Too long!
Comments