Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Simplify Large Queries with Temporary Tables, Table Variables and CTEs

By Stan Kulp,

Large, complex queries involving multiple joins, unions and nested subqueries can get incomprehensible fast, and can be extremely difficult to validate, revise or troubleshoot. Views are a means of simplifying such queries, but it is impractical to create new views for every query you write. Temporary tables, table variables and common (derived) table expressions (CTEs) can be thought of as ad hoc views that can be created on demand.

The following script creates and populates three standard related tables, then shows how these tables can be joined into a single temporary table, table variable or CTE that can be used in subsequent query operations. To run the script, simply copy-and-paste it into a query editor window with read/write privileges in SQL Server Management Studio and execute it.

Demonstration Script 1: Temporary Table, Table Variable and CTE Examples

/*
	Demonstration Script 1: Temporary Table, Table Variable and CTE Examples

	THIS SCRIPT CREATES AND POPULATES THREE RELATED STANDARD TABLES, THEN CREATES 
	AND POPULATES A TEMPORARY TABLE, A TABLE VARIABLE AND A COMMON TABLE EXPRESSION
	USING JOINS OF THE THREE STANDARD TABLES TO MERGE ALL THE DATA INTO SINGLE 
	TABLE OBJECTS.
*/

--CREATE AND POPULATE TEST TABLES

BEGIN TRY
DROP TABLE RetailerIdentification
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE RetailerIdentification 
(
 RetailerId INT,
 BusinessName VARCHAR(50)
)

INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 12345,'McDonalds'
INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 63643,'Hardees'
INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 38348,'Kentucky Fried Chicken'
INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 23984,'Pizza Hut'

SELECT 'Table' AS TableObjectType,'RetailerIdentification' AS TableObjectName,RetailerId,BusinessName FROM RetailerIdentification

GO

BEGIN TRY
DROP TABLE RetailerLocation
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE RetailerLocation 
(
 RetailerId INT,
 Location VARCHAR(50)
)

INSERT INTO RetailerLocation (RetailerId,Location) SELECT 12345,'Des Plaines'
INSERT INTO RetailerLocation (RetailerId,Location) SELECT 63643,'Greenville'
INSERT INTO RetailerLocation (RetailerId,Location) SELECT 38348,'South Salt Lake'
INSERT INTO RetailerLocation (RetailerId,Location) SELECT 23984,'Wichita'

SELECT 'Table' AS TableObjectType,'RetailerLocation' AS TableObjectName,RetailerId,Location FROM RetailerLocation

GO

BEGIN TRY
DROP TABLE RetailerSales
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE RetailerSales 
(
 RetailerId INT,
 Sales MONEY
)

INSERT INTO RetailerSales (RetailerId,Sales) SELECT 12345,8273641.28
INSERT INTO RetailerSales (RetailerId,Sales) SELECT 63643,2388732.83
INSERT INTO RetailerSales (RetailerId,Sales) SELECT 38348,3292384.62
INSERT INTO RetailerSales (RetailerId,Sales) SELECT 23984,7348934.48

SELECT 'Table' AS TableObjectType,'RetailerSales' AS TableObjectName,RetailerId,Sales FROM RetailerSales

GO

--CREATE AND POPULATE TEMPORARY TABLE

BEGIN TRY
DROP TABLE ##TemporaryTable
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE ##TemporaryTable 
(
 RetailerId INT,
 BusinessName VARCHAR(50),
 Location VARCHAR(50),
 Sales MONEY
)

INSERT INTO ##TemporaryTable (RetailerId,BusinessName,Location,Sales)
SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales
FROM RetailerIdentification a
INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId

SELECT 
	'TemporaryTable' AS TableObjectType,
	'##TemporaryTable' AS TableObjectName,
	RetailerId,BusinessName,
	Location,
	Sales 
FROM 
	##TemporaryTable

GO

--CREATE AND POPULATE TABLE VARIABLE

DECLARE @TableVariable TABLE
( 
 RetailerId INT,
 BusinessName VARCHAR(50),
 Location VARCHAR(50),
 Sales MONEY
)

INSERT INTO @TableVariable (RetailerId,BusinessName,Location,Sales)
SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales
FROM RetailerIdentification a
INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId

SELECT 
	'TableVariable' AS TableObjectType,
	'@TableVariable' AS TableObjectName,
	RetailerId,
	BusinessName,
	Location,
	Sales 
FROM 
	@TableVariable

GO

--CREATE AND POPULATE COMMON TABLE EXPRESSION

WITH CTE (RetailerId,BusinessName,Location,Sales)
AS 
(
 SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales
 FROM RetailerIdentification a
 INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
 INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId
)

SELECT 
	'CommonTableExpression' AS TableObjectType,
	'CTE' AS TableObjectName,
	RetailerId,BusinessName,
	Location,
	Sales 
FROM 
	CTE

GO

DROP TABLE RetailerIdentification
DROP TABLE RetailerLocation
DROP TABLE RetailerSales

GO

/*
	THE TEMPORARY TABLE STILL EXISTS AFTER EXECUTION, THE TABLE VARIABLE AND CTE DO NOT
	
	PROVE IT BY EXECUTING THE FOLLOWING STATEMENTS

	SELECT * FROM ##TemporaryTable 
	SELECT * FROM @TableVariable
	SELECT * FROM CTE
	
	DROP TABLE ##TemporaryTable
*/

Results of Demonstration Script 1 Execution

For all practical purposes, the table objects we just created are tables that we can use to join with other tables in queries, just like standard tables.

One of the objects, ##TemporaryTable, still exists after the script finishes execution, as you can see in the Management Studio Object Explorer:

You can also prove it by inserting and running the following query in the same SQL editor window as the script:

SELECT * FROM ##TemporaryTable

If you try the same thing with either of the other table objects, you will confirm that they no longer exist:

SELECT * FROM @TableVariable
SELECT * FROM CTE

You can drop the temporary table with the statement:

DROP TABLE ##TemporaryTable

Which one do you use? That depends upon how you are going to use it.

  1. A temporary table is available as long as the database connection with which it was executed is still open. If it is declared with the "##" prefix, it is a global variable available to other sessions. If it is declared with the "#" prefix, it is a local variable available only in the current session.
  2. A table variable is only available while the script that contains it is running.
  3. A CTE is only available while the statement that contains it is running.

Table variables and CTEs are stored in memory (if sufficient memory is available), while temporary tables are always stored on disk.

Joining a Table Object with Another Table

Once we have built a temporary table, table variable or CTE, we can join it with another table, as demonstrated in the following script.

Demonstration Script 2: Joining a Table Object with Another Table

/*
	Demonstration Script 2: Joining a Table Object with Another Table

	THIS SCRIPT CREATES AND POPULATES THE THREE RELATED STANDARD TABLES FROM THE
	PREVIOUS DEMONSTRATION SCRIPT, AS WELL AS A FOURTH RELATED STANDARD TABLE. IT 
	THEN POPULATES THE SAME TEMPORARY TABLE, TABLE VARIABLE AND COMMON TABLE 
	EXPRESSION AS THE PREVIOUS SCRIPT, AND JOINS THOSE TABLE OBJECTS WITH THE NEW
	TABLE.
*/

--CREATE AND POPULATE TEST TABLES

BEGIN TRY
DROP TABLE RetailerIdentification
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE RetailerIdentification 
(
 RetailerId INT,
 BusinessName VARCHAR(50)
)

INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 12345,'McDonalds'
INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 63643,'Hardees'
INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 38348,'Kentucky Fried Chicken'
INSERT INTO RetailerIdentification (RetailerId,BusinessName) SELECT 23984,'Pizza Hut'

--SELECT 'Table' AS TableObjectType,'RetailerIdentification' AS TableObjectName,RetailerId,BusinessName FROM RetailerIdentification

GO

BEGIN TRY
DROP TABLE RetailerLocation
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE RetailerLocation 
(
 RetailerId INT,
 Location VARCHAR(50)
)

INSERT INTO RetailerLocation (RetailerId,Location) SELECT 12345,'Des Plaines'
INSERT INTO RetailerLocation (RetailerId,Location) SELECT 63643,'Greenville'
INSERT INTO RetailerLocation (RetailerId,Location) SELECT 38348,'South Salt Lake'
INSERT INTO RetailerLocation (RetailerId,Location) SELECT 23984,'Wichita'

--SELECT 'Table' AS TableObjectType,'RetailerLocation' AS TableObjectName,RetailerId,Location FROM RetailerLocation

GO

BEGIN TRY
DROP TABLE RetailerSales
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE RetailerSales 
(
 RetailerId INT,
 Sales MONEY
)

INSERT INTO RetailerSales (RetailerId,Sales) SELECT 12345,8273641.28
INSERT INTO RetailerSales (RetailerId,Sales) SELECT 63643,2388732.83
INSERT INTO RetailerSales (RetailerId,Sales) SELECT 38348,3292384.62
INSERT INTO RetailerSales (RetailerId,Sales) SELECT 23984,7348934.48

--SELECT 'Table' AS TableObjectType,'RetailerSales' AS TableObjectName,RetailerId,Sales FROM RetailerSales

GO

BEGIN TRY
DROP TABLE BusinessInfo
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE BusinessInfo 
(
 BusinessName VARCHAR(50),
 BusinessFounder VARCHAR(50)
)

INSERT INTO BusinessInfo (BusinessName,BusinessFounder) SELECT 'McDonalds','Ray Kroc'
INSERT INTO BusinessInfo (BusinessName,BusinessFounder) SELECT 'Hardees','Wilbur Hardee'
INSERT INTO BusinessInfo (BusinessName,BusinessFounder) SELECT 'Kentucky Fried Chicken','Harland Sanders'
INSERT INTO BusinessInfo (BusinessName,BusinessFounder) SELECT 'Pizza Hut','Dan and Frank Carney'

--SELECT 'Table' AS TableObjectType,'BusinessInfo' AS TableObjectName,BusinessName,BusinessFounder FROM BusinessInfo

GO

--EXECUTE FOUR TABLE JOIN

SELECT '4-Table Join' AS JoinType,a.RetailerId,a.BusinessName,b.Location,c.Sales,d.BusinessFounder
FROM RetailerIdentification a
INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId
INNER JOIN BusinessInfo d ON a.BusinessName = d.BusinessName

--CREATE AND POPULATE TEMPORARY TABLE

BEGIN TRY
DROP TABLE ##TemporaryTable
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE ##TemporaryTable 
(
 RetailerId INT,
 BusinessName VARCHAR(50),
 Location VARCHAR(50),
 Sales MONEY
)

INSERT INTO ##TemporaryTable (RetailerId,BusinessName,Location,Sales)
SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales
FROM RetailerIdentification a
INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId

SELECT 
	'Temporary Table Joined With New Table' AS JoinType,
	a.RetailerId,a.BusinessName,
	a.Location,
	a.Sales,
	b.BusinessFounder
FROM 
	##TemporaryTable a
INNER JOIN 
	BusinessInfo b ON a.BusinessName = b.BusinessName

GO

--CREATE AND POPULATE TABLE VARIABLE

DECLARE @TableVariable TABLE
( 
 RetailerId INT,
 BusinessName VARCHAR(50),
 Location VARCHAR(50),
 Sales MONEY
)

INSERT INTO @TableVariable (RetailerId,BusinessName,Location,Sales)
SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales
FROM RetailerIdentification a
INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId

SELECT 
	'Table Variable Joined With New Table' AS JoinType,
	a.RetailerId,a.BusinessName,
	a.Location,
	a.Sales,
	b.BusinessFounder
FROM 
	@TableVariable a
INNER JOIN 
	BusinessInfo b ON a.BusinessName = b.BusinessName

GO

--CREATE AND POPULATE COMMON TABLE EXPRESSION

WITH CTE (RetailerId,BusinessName,Location,Sales)
AS 
(
 SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales
 FROM RetailerIdentification a
 INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
 INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId
)

SELECT 
	'CTE Joined With New Table' AS JoinType,
	a.RetailerId,a.BusinessName,
	a.Location,
	a.Sales,
	b.BusinessFounder
FROM 
	CTE a
INNER JOIN 
	BusinessInfo b ON a.BusinessName = b.BusinessName

GO

DROP TABLE RetailerIdentification
DROP TABLE RetailerLocation
DROP TABLE RetailerSales

GO

Results of Demonstration Script 2 Execution

The result of joining the three tables from the previous script and a new table is the same as joining any of the previous table objects with the new table, but the object joins are easier to decipher.

Joining the four standard tables looks like this:

SELECT a.RetailerId,a.BusinessName,b.Location,c.Sales,d.BusinessFounder
FROM RetailerIdentification a
INNER JOIN RetailerLocation b ON a.RetailerId = b.RetailerId
INNER JOIN RetailerSales c ON a.RetailerId = c.RetailerId
INNER JOIN BusinessInfo d ON a.BusinessName = d.BusinessName

While joining the previous table objects with the new table looks like this:

SELECT a.RetailerId,a.BusinessName, a.Location,a.Sales,b.BusinessFounder
FROM ##TemporaryTable a
INNER JOIN BusinessInfo b ON a.BusinessName = b.BusinessName SELECT a.RetailerId,a.BusinessName, a.Location,a.Sales,b.BusinessFounder
FROM @TableVariable a
INNER JOIN BusinessInfo b ON a.BusinessName = b.BusinessName SELECT a.RetailerId,a.BusinessName, a.Location,a.Sales,b.BusinessFounder
FROM CTE a
INNER JOIN BusinessInfo b ON a.BusinessName = b.BusinessName

Conclusion

This is an admittedly trivial example, but the underlying principle of building large queries in cascading stages using table objects will make it much easier to decipher the logic of a complicated query at a later date.

MSDN Links

Total article views: 9433 | Views in the last 30 days: 7
 
Related Articles
FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

DYNAMIC SELECT & INSERT QUERY HELP

DYNAMIC SELECT & INSERT QUERY HELP

FORUM

Inserting RAW Data to Table with selected columns

insert a RAW Data to a Table with selected columns

FORUM

Selecting from four columns in different table

select and insert

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones