Temporary table VS table variables: suitable for use (1)

Temporary table VS table variables: suitable for use (1)

For a long time, there has been a lot of debate over the advantages and disadvantages of temporary tables and table variables. Some friends in the Technical Group even think that table variables are almost useless, such as no statistical information or transactions. but this is not the case. here I will make a comparison between the temporary table and the table variables, and describe in detail where most people do not understand or have ambiguity.

Note: Only temporary tables are discussed here.

Lifecycle

Temporary table: in session, In proc, or using explicit drop

Table variable: in batch

Here, a simple code is used to describe the scope of table variables.

 
 

  
  
DECLARE @ t TABLE (I int) ---- Define TABLE variable @ t
  
  
 
  
  
SELECT * FROM @ t ----- access OK
  
  
 
  
  
Insert into @ t select 1 ----- insert data OK
  
  
 
  
  
Select * from @ t ------- access OK
  
  
Go ------- end Batch Processing
  
  
Select * from @ t ------- error in out-of-scope
 
 

Note: Although sqlserver does not allow you to use the Defined variables before defining the table variables, you can still run normally in the following situations!

 
 

  
  
If 'A' = 'B'
  
  
Begin
  
  
DECLARE @ t TABLE (I int)
  
  
End
  
  
SELECT * FROM @ t ----- still accessible!
 
 

Log Mechanism

Both temporary tables and table variables are logged in tempdb.

The difference is that the active logs of the temporary table cannot be truncated before the transaction is completed.

Note that table variables do not support truncate, so temporary tables have obvious advantages when the object result set is completely cleared, while table variables can only be deleted.

Transaction support

Temporary table: supported

Table variable: not supported

We use simple examples to describe

 
 

  
  
create table #t (i int)  
  
  
declare @t table(i int)  
  
  
 
  
  
BEGIN TRAN ttt  
  
  
insert into #t select 1  
  
  
insert into @t select 1  
  
  
SELECT * FROM #t  ------returns 1 rows  
  
  
SELECT * FROM @t  ------returns 1 rows  
  
  
ROLLBACK tran ttt  
  
  
 
  
  
SELECT * FROM #t    -------no rows  
  
  
SELECT * FROM @t    -------still 1 rows  
  
  
drop table #t       ----no use drop @t in session 
 
 

Select)

The temporary table will add the IS (intention sharing) lock to the relevant object.

The table variable adds the SCH-S (schema share) lock to the relevant object (equivalent to adding the nolock hint)

It can be seen that although the impact of the lock range IS different, but because the scope IS only session or batch, temporary table IS lock although the compatibility IS not as good as the SCH-S of the table variables, but the vast majority of the situation basically no impact.

If you are interested, you can use TF1200 for testing.

Index support

Temporary table support

Table variable conditions supported (only SQL2014)

In sql2014, you can create Index 1-1 while creating a table.

Note: Before sql2014, only one default uniqueness constraint can be created for table variables.

Cod

 
 

  
  
DECLARE @t TABLE   
  
  
(  
  
  
col1 int index inx_1 CLUSTERED,   
  
  
col2 int  index index_2 NONCLUSTERED,  
  
  
       index index_3 NONCLUSTERED(col1,col2)  
  
  
) 
 
 

Figure 1-1

 
 

  
  
CREATE FUNCTION TVP_Customers (@cust nvarchar(10))  
  
  
RETURNS TABLE 
  
  
AS 
  
  
 RETURN 
  
  
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  
  
 FROM BigOrders  
  
  
 WHERE CustomerID = @cust);  
  
  
GO  
  
  
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))  
  
  
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,  
  
  
 ShipCountry nvarchar(30))  
  
  
AS 
  
  
BEGIN 
  
  
 INSERT INTO @T  
  
  
  SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  
  
  FROM BigOrders  
  
  
  WHERE CustomerID = @cust  
  
  
  RETURN 
  
  
END;  
  
  
 
  
  
DBCC FREEPROCCACHE  
  
  
GO  
  
  
SELECT * FROM TVF_Customers('CENTC');  
  
  
GO  
  
  
SELECT * FROM TVP_Customers('CENTC');  
  
  
GO  
  
  
SELECT * FROM TVF_Customers('SAVEA');  
  
  
GO  
  
  
SELECT * FROM TVP_Customers('SAVEA');  
  
  
GO  
  
  
 
  
  
select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a  
  
  
cross apply sys.dm_exec_sql_text(a.sql_handle) b  
  
  
where b.text like '%_Customers%' 
 
 

Figure 1-2

Other aspects

Table variables do not support select into, alter, truncate, dbcc, etc.

Table variables do not support table hint such as (force seek)

Execution Plan Estimation

I think this may be the reason for a strong debate on the method used. Because the table variables do not have statistical information and indexes cannot be added, you may sneer at the performance of the table variables in the execution plan, but what is the actual situation? In-depth analysis is required.

I will not describe the estimation of temporary tables here, but mainly describe the estimation of table variables in detail.

One reason for the introduction of table variables in sql2000 is to reduce re-compilation during some execution processes. for better performance. of course, the benefits will also bring some drawbacks. because it does not involve recompilation, the optimizer does not know the specific number of rows in the table variables. At this time, he adopted a conservative estimation method: the estimated number of rows is one row. 2-1

Code

 
 

  
  
Declare @ t table (I int)
  
  
Select * from @ t ----- at this time, the estimated number of rows in line 0 is 1
  
  
Insert into @ t select 1
  
  
Select * from @ t ----- at this time, 1 row, the estimated number of rows is still 1 row
  
  
Insert into @ t values (2), (3), (4), (5), (6), (7), (8), (9 ), (10), (11), (12), (14), (15), (16), (17), (18), (19), (20)
  
  
Select * from @ t ---- at this time, 19 rows, the estimated number of rows is still 1
  
  
 
  
  
--... No matter how many rows exist in the actual @ t, it is estimated to be 1 line because there is no re-compilation.
 
 

Figure 2-1

So when we add the re-compilation operation, the optimizer will know the specific number of rows of table variables. 2-2

Code

 
 

  
  
Declare @ t table (I int)
  
  
Select * from @ t option (recompile) ----- at this time, the estimated number of rows in row 0 is 1.
  
  
Insert into @ t select 1
  
  
Select * from @ t option (recompile) ----- at this time, 1 row, the estimated number of rows is 1 row
  
  
Insert into @ t values (2), (3), (4), (5), (6), (7), (8), (9 ), (10), (11), (12), (14), (15), (16), (17), (18), (19), (20)
  
  
Select * from @ t option (recompile) ---- 19 rows at this time, and the estimated number of rows is 19
  
  
--... When hint is added, the optimizer will know the number of rows of table variables.
 
 

Figure 2-2

Now, we can see that the optimizer knows the number of rows in the table variables. in this way, in the process of table variable scanning, especially when the data volume is large, it will not cause some problems because the prediction is always 1.

If you just know the estimation principle and the existing code is re-compiled, you can imagine the workload. Here we will introduce a new Trace tag, Trace Flag 2453.

TF2453 can replace Hint for a certain extent, but only in the case of a simple trivial plans)

Note: TF2453 only applies to patches in sql2012 SP2 and SQL2014.