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.
- 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.
- A table variable is only available while the script that contains it is running.
- 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.
