In SQL Server, there is no concept about a memory table. Table Variables are also created in TempDB. The key differences and some execution behaviors between the Table Variable and the Temporary Table are as follows:
1. A Table variable is transient in nature; it does not need to be explicitly dropped.
2. A Table variable starts with “@” whereas a Temporary table would start with “#”. The Global temporary table will have “##” and on the other hand a Table variable can’t be global.
3. Once created, definitions can’t be changed.
4. Primary, Clustered and non-Clustered keys are allowed while defining the table variable. A Temporary Table has similar attributes to a regular table and definitions can be changed.
5. Statistics can’t be created on a Table variable, therefore does not provide any cardinality estimate.
6. A Table variable does use a transaction log; however a Transaction (COMMIT/ROLLBACK) has no effect.
7. TRUNCATE command can’t be used; only DELETE command can be used.
8. Table variables are more prone to create a page allocation issue in TempDB than Temporary tables.
9. Table variables are cleaned up automatically at the end of the function, a stored procedure, or a batch in which they are defined.
10. CHECK constraints, DEFAULT values and computed columns in a table type declaration can’t call user-defined functions.
11. Table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
12. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
13. A Table Variable may not be used in the following statements:
INSERT INTO @table_variable EXEC stored_procedure SELECT select_list INTO @table_variable FROM … statements.
14. In TempDB, usually a table variable creates 9 characters to identify its uniqueness for each session where the first character is # and the rest are eight characters in alpha numeric. Whereas a Temporary table can be identified with a leading # along its original name followed by a number of underscore symbol and lastly with some 0’s. For example
Table Variable: #AHG8TTRW, #RFDAS37D
Temporary Table: #tmp________________________00000000001
Suitability of a Table Variable:
1. Usually better suited where very few rows (500 records or less) need intermediate processing.
2. Where CTE (common table expression) or Derived Tables (inline table) are not possible to use.
3. Does not require joining with a large table and multiple times.
4. To avoid recompilation and LOCK contention in a busy OLTP system.
Verifying a Table Variable Creation:
Let’s see where a table variable creates. We will be using the following steps to verify its existence with the “sys.fn_PhysLocFormatter”.
(a) Create a database “tempdb” and then create a table variable and insert some records in it.
(b) We will easily find the physical location of each record where it is being created.
CREATE DATABASE TestDB
GO
USE TestDB
GO
-- create table variable
DECLARE @tv TABLE
(
nID INT IDENTITY(1, 1) NOT NULL ,
col1 INT ,
col2 VARCHAR(10) ,
PRIMARY KEY CLUSTERED ( nID ) ,
UNIQUE NONCLUSTERED ( Col1 )
)
-- insert some rows
INSERT INTO @tv ( col1, col2 ) SELECT 1 , 'abc'
INSERT INTO @tv ( col1, col2 ) SELECT 2 , 'xyz'
INSERT INTO @tv ( col1, col2 ) SELECT 3 , 'asd'
INSERT INTO @tv ( col1, col2 ) SELECT 4 , 'mnp'
-- check the location of each record
SELECT nID, col1, col2, sys.fn_PhysLocFormatter(%%physloc%%) RID FROM @tv
Here is another verification to check its existence in TempDB. We can use “STATISTICS IO” output to identify the table creation and note the "#" table name from the message window in SSMS and query the “sys.tables” in TempDB to find its existence.
SET STATISTICS IO ON
GO
DECLARE @tv TABLE
(
col1 INT ,
col2 VARCHAR(10) ,
PRIMARY KEY CLUSTERED ( COL1 ) ,
UNIQUE NONCLUSTERED ( Col1 )
)
INSERT INTO @tv
( col1, col2 )
VALUES ( 1, 'AAAA' ),
( 2, 'BBBB' ),
( 3, 'CCCC' ),
( 4, 'EEEE' )
SELECT * FROM @tv
SELECT * FROM tempdb.sys.tables
Transaction Log usage:
(a) Table variable uses log file for its DML operation
(b) A checkpoint begin/end operation on Log file can be identified by using the” fn_dblog”
(c) Execute DBCC SQLPERF to see the difference. The “percentage of log space” can be used to identify how much log space has been used.
Following is the script we can use to observe above behavior.
DECLARE @tv TABLE
(
col1 INT ,
col2 VARCHAR(1000) ,
col3 VARCHAR(1000)
)
DECLARE @n INT
SET @n = 1
WHILE ( @n <= 10000 )
BEGIN
INSERT INTO @tv
( col1 ,
col2 ,
col3
)
SELECT @n ,
REPLICATE('A', 999) ,
REPLICATE('B', 999)
SET @n = @n + 1
END
DBCC SQLPERF (LOGSPACE)
SELECT * FROM fn_dblog(NULL, NULL) AS dblog WHERE [Operation] LIKE '%CKPT'
Transaction Handling:
Though there is a log operation happening against a Table Variable on a transaction log file, ROLLBACK will not work.
Though there is a log operation happening against a Table Variable on a transaction log file, ROLLBACK will not work.
DECLARE @tv TABLE
(
nID INT IDENTITY(1, 1) NOT NULL ,
col1 INT ,
col2 VARCHAR(10) ,
PRIMARY KEY CLUSTERED ( nID ) ,
UNIQUE NONCLUSTERED ( Col1 )
)
--will return nothing as there are no records
SELECT * FROM @tv
BEGIN TRANSACTION
-- insert some rows
INSERT INTO @tv ( col1, col2 ) SELECT 10 , 'abc'
INSERT INTO @tv ( col1, col2 ) SELECT 20 , 'xyz'
INSERT INTO @tv ( col1, col2 ) SELECT 30 , 'asd'
INSERT INTO @tv ( col1, col2 ) SELECT 40 , 'mnp'
ROLLBACK TRANSACTION
-- Rollback did not work, will return 4 records
SELECT * FROM @tv
-- Try to update column"col2"
BEGIN TRANSACTION
UPDATE @tv SET col2='DUMMY'
ROLLBACK TRANSACTION
-- Rollback did not work
SELECT * FROM @tv
Cardinality Estimation is always wrong:
The number of rows in the table variable is unknown when a batch or stored procedure containing the table variable is compiled. Thus SQL Server optimizer has to make some assumptions and it usually estimates a very low number of rows for the table variable. As Statistics can’t be created on a table variable thus it introduces in-efficient query execution with wrong cardinality estimate in every execution. However, using a statement level optimizer hints “OPTION (RECOMPILE)” which will help to rectify the wrong cardinality estimate.
DECLARE @tv TABLE
(
[name] NVARCHAR(70) ,
[number] INT
)
INSERT INTO @tv
SELECT [name] ,
[number]
FROM master..spt_values
WHERE [name] IS NOT NULL
-- select data from @tv
SELECT * FROM @tv -- OPTION (RECOMPILE)
Read More:
table (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms175010.aspx
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/kb/305977
No comments:
Post a Comment