Table of Contents




DOWNLOAD
the
CODE

All Codes used in this article is downloadable from this URL.

 

Introduction

This article explains two possible reasons to use a table variable rather than a temporary table.

Problem 1 - User Defined Data Types

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;
GO
 
--UserDefinedDataType
CREATE TYPE Tag FROM CHAR(8) NOT NULL;
GO
 
--table
CREATE 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

CREATE TABLE #Book
( BookId INT PRIMARY KEY,
  Code Tag ,
  Title NVARCHAR(500) );

Pic 01

Solution

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 TABLE
( BookId INT PRIMARY KEY,
  Code Tag ,
  Title NVARCHAR(500) );

Problem 2 – Mixed Collation

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
CREATE DATABASE TestCol
  COLLATE SQL_Latin1_General_CP1_CS_AS
GO
 
USE TestCol
GO
 
CREATE TABLE Books
( BookId INT PRIMARY KEY,
  Title NVARCHAR(500) );
GO
 
INSERT dbo.Books
        ( BookId, Title )
VALUES  ( 1, 'New Life')
 
GO

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

CREATE TABLE #Books
( BookId INT PRIMARY KEY,
  Title NVARCHAR(500) );
 
INSERT #Books
        ( BookId, Title )
SELECT BookId ,
       Title
FROM dbo.Books;
 
--from temp table
SELECT *
FROM #Books
WHERE Title LIKE '%new%'
 
--from base table
SELECT *
FROM Books
WHERE Title LIKE '%new%'

Pic 02

 

Solution

Using a Table Variable instead of using the Temp Table will fix this issue. Change that script to:

Script 06

DECLARE @Books TABLE
( BookId INT PRIMARY KEY,
  Title NVARCHAR(500) );
 
INSERT @Books
        ( BookId, Title )
SELECT BookId ,
       Title
FROM dbo.Books;
 
--from table variable
SELECT *
FROM @Books
WHERE Title LIKE '%new%'
 
--from base table
SELECT *
FROM Books
WHERE Title LIKE '%new%'

Conclusion

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. 

 

DOWNLOAD
the
CODE

All Script used in this article can be downloaded from this URL.

 


See Also

Other Resources