DOWNLOAD the CODE
All Codes used in this article is downloadable from this URL.
This article explains two possible reasons to use a table variable rather than a temporary table.
If we use User Defined Data Types in our database design, sooner or later, will find that we cannot use them in temp tables. We can see this with a sample by using the following script:
Script 01
CREATE
DATABASE
UDDT ;
GO
USE UDDT;
--UserDefinedDataType
TYPE Tag
FROM
CHAR
(8)
NOT
NULL
;
--table
TABLE
Books
( BookId
INT
PRIMARY
KEY
,
Code Tag ,
Title NVARCHAR(500) );
Now, we can try to create a temp table. But as shown in the next picture, we cannot do this. The following script demonstrates this:
Script 02
#Book
Pic 01
A workaround for this problem is to use a Table Variable instead of using the Temp Table. Changing that script to:
Script 03
DECLARE
@Book
If we have a SQL Server instance which serves multiple databases with different collations, we will find conflicting collations cause issues. One problem is with creating temp tables. We can see this situation with the following example. In this sample, we assumed that the SQL Server Instance Collation is SQL_Latin1_General_CP1_CI_AS.
Script 04
-- case sensitive
TestCol
COLLATE
SQL_Latin1_General_CP1_CS_AS
USE TestCol
INSERT
dbo.Books
( BookId, Title )
VALUES
( 1,
'New Life'
)
We can create a temp table to test our scenario. The next code shows this. As shown in the next picture, our query from the temp table shows the wrong result. The reason is that the collation in temp table is the same collation in the tempdb database which is case insensitive. But our test database has a case-sensitive collation.
Script 05
#Books
SELECT
BookId ,
Title
dbo.Books;
--from temp table
*
WHERE
LIKE
'%new%'
--from base table
Pic 02
Using a Table Variable instead of using the Temp Table will fix this issue. Change that script to:
Script 06
@Books
--from table variable
We saw two reasons for using table variables rather than temp tables. There are also reasons for using temp tables instead of table variables. Generally speaking, we should choose temp tables where they work but this will not be the best choice in absolutely every circumstance. We have two object types each with their own strengths and weaknesses. We should understand these and choose between them based on our database design and environment.
All Script used in this article can be downloaded from this URL.