Chapter 8 - Temporary Tables
“Graffiti’s always been a temporary art form. You make your mark and then they scrub it off.”
- Banksy
There are three types of Temporary Tables in TEMPDB
Private Temporary Table
Is always created as #tablename
Space comes from tempdb
Can only be used by the connection that created the table
Can be created by the User, then populated with an INSERT/SELECT
Table and Data are deleted after the connection that created the table is
closed
Global Temporary Table
Is always created as ##tablename
Space comes from tempdb
Can be used by any connection Can be created by the User, then
populated with an INSERT/SELECT
Table and Data are deleted after the creating connection is closed and all
queries running against the table complete
Direct Temporary Table
Is always created as tempdb.tablename
Space comes from tempdb
Can be used by anyone who has specifically been granted access.
Can be created by the User, then populated with an INSERT/SELECT
Table and Data exist after the creating connection is closed
The three types of Temporary tables in TEMPDB are Private, Global, and Direct Temporary Tables.
Tables in TEMPDB are not your only Temporary Storage
Derived Table
Is a SELECT statement within a SELECT statement
Is purely logical as opposed to physical
Exists only within a query
Has its execution optimized at run time along with the rest of a query
Common Table Expression(CTE)
Is defined by using a WITH statement
Can be referenced numerous times in a query
Is purely logical as opposed to physical
Exists only within a query
Has its execution optimized at run time along with the rest of a query
Can be used for recursive queries
Table Variables
Are variables
Are written to disk in tempdb
Exist for the duration of the batch that holds them
Perform better for smaller datasets
Data can be stored temporarily in Derived Tables, Common Table Expressions, and Table Variables.
What is TEMPDB?
TEMPDB is a database similar to all other SQL Server databases
It is recreated every time SQL Server is started
Allows for transactions to be rolled back, but does not allow for database recovery
Because of limited logging, operations in TEMPDB can be much faster than in other databases
Is the storage location for private, global, and
direct temporary tables; as well as table variables
Like most things in life, TEMPDB is temporary. It is wonderful for temporary data storage. Just do not count on it having data that will be there for you in the future.
Creating a Private Temporary Table
Only you or your connection can use a Private Temporary Table.!
You Populate a Private Temporary Table with an INSERT/SELECT
Private Temporary Tables can be
Materialized with
An Insert/Select
statement
1) A USER Creates a Private Temporary Table and then 2) populates it with an INSERT/SELECT Statement. The space to materialize this table comes from tempdb. Now you can query this table all session long. When the session is logged off the table and the data are automatically deleted.
The Three Steps to Use a Private Temporary Table
1) A USER Creates a Private Temporary Table and then 2) populates the it with an INSERT/SELECT Statement, and then 3) Queries it until Logging off.
Creating a Global Temporary Table
All connections can use a Global Temporary Table! Think of it as not being private at all.
You Populate a Global Temporary Table with an INSERT/SELECT
Global Temporary Tables can be
Materialized with
An Insert/Select
statement
1) A USER Creates a Global Temporary Table and then 2) populates it with an INSERT/SELECT Statement. The space to materialize this table comes from tempdb. Any connection can query this table. When the creating connection is logged off and the last active query using the table completes the table and the data are automatically deleted.
The Three Steps to Use a Global Temporary Table
1) A USER Creates a Global Temporary Table and then 2) a user populates the it with an INSERT/SELECT Statement, and then 3) everyone can query it until the creating user logsoff and the last active query completes.
Creating a Direct Temporary Table
Only users that have specifically been granted access can use a Direct Temporary Table! This allows you to protect your data. Access to your data is limited to people who should be allowed to see your data.
You Populate a Direct Temporary Table with an INSERT/SELECT
Direct Temporary Tables can be
Materialized with
An Insert/Select
statement
1) A USER Creates a Direct Temporary Table and then 2) GRANTS users access and then 3) a user populates it with an INSERT/SELECT Statement. 4) Direct Temporary Tables remain available until they are DROPPED or the SQL Server is stopped and restarted.
The Three Steps to Use a Direct Temporary Table
1) A USER Creates a Direct Temporary Table and then 2) GRANTS users access and then 3) a user populates it with an INSERT/SELECT Statement. 4) Direct Temporary Tables remain available until they are DROPPED or the SQL Server is stopped and restarted.
CREATING A Derived Table
•Is a SELECT Statement with a SELECT Statement
•Is purely logical as opposed to physical
•Exists only within a query
•Has its execution optimized at run time along with the rest of a query
The SELECT Statement that creates and populates the Derived table is always inside Parentheses.
Naming the Derived Table
In the example above, TeraTom is the name we gave the Derived Table. It is mandatory that you always name the table or it errors.
Aliasing the Column Names in the Derived Table
AVGSAL is the name we gave to the column in our Derived Table that we call TeraTom. Our SELECT (which builds the columns) shows we are only going to have one column in our derived table, and we have named that column AVGSAL.
Multiple Ways to Alias the Columns in a Derived Table
CREATING a Derived Table using the WITH Command
When using the WITH Command, we can CREATE our Derived table while running the main query.
The Same Derived Query shown Three Different Ways
MULTIPLE Derived Tables using the WITH Command
Using the WITH Command, we can CREATE multiple Derived tables that can be referenced elsewhere in the query.
Column Alias Can Default For Normal Columns
In a derived table, you will always have a SELECT query in parenthesis, and you will always name the table. You have options when aliasing the columns. As in the example above, you can let normal columns default to their current name.
Most Derived Tables Are Used To Join To Other Tables
The first five columns in the Answer Set came from the Employee_Table. AVGSAL came from the derived table named TeraTom
A Join Example Showing Different Column Alias Styles
The Three Components of a Derived Table
|
A derived table is a SELECT query. The SELECT query always starts with an open parenthesis and ends with a close parenthesis. |
|
The derived table must be given a name. Above we called our derived table TeraTom. |
|
You will need to define (alias) the columns in the derived table. Above we could allow Dept_No to default to Dept_No, but we had to specifically alias AVG(Salary) as AVGSAL. |
Every derived table must have the three components listed above
Visualize This Derived Table
Our example above shows the data in the derived table named TeraTom. This query allows us to see each employee and the plus or minus avg of their salary compared to the other workers in their department.
Our Join Example With The WITH Syntax
Now, the lower portion of the
query refers to TeraTom
Almost like it is a permanent
table, but it is not!
Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROM Employee_Table
GROUP BY Dept_No) as TeraTom (Depty, AVGSAL)
ON Dept_No = Depty ;
1) What is the name of the derived table? _________
2) How many columns are in the derived table? _________
3) What is the name of the derived table columns? _________
4) Is there more than one row in the derived table? _________
5) What common keys join the Employee and Derived? _________
6) Why were the join keys named differently? ___________
Answer to Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROM Employee_Table
GROUP BY Dept_No) as TeraTom (Depty, AVGSAL)
ON Dept_No = Depty ;
1) What is the name of the derived table? TeraTom
2) How many columns are in the derived table? 2
3) What’s the name of the derived columns? Depty and AVGSAL
4) Is their more than one row in the derived table? Yes
5) What keys join the tables? Dept_No and Depty
6) Why were the join keys named differently? If both were named Dept_No, we would error unless we full qualified.
Clever Tricks on Aliasing Columns in a Derived Table
A Derived Table lives only for the lifetime of a single query
The semi-colon (;) indicates the end of the query.
An Example of Two Derived Tables in a Single Query
WITH T (Dept_No, AVGSAL) AS
(SELECT Dept_No, AVG(Salary) FROM Employee_Table
GROUP BY Dept_No)
SELECT T.Dept_No, First_Name, Last_Name,
AVGSAL, Counter
FROM Employee_Table as E
INNER JOIN
T
ON E.Dept_No = T.Dept_No
INNER JOIN
(SELECT Employee_No, SUM(1) OVER(PARTITION BY Dept_No
ORDER BY Dept_No, Last_Name Rows Unbounded Preceding)
FROM Employee_Table) as S (Employee_No, Counter)
ON E.Employee_No = S.Employee_No
ORDER BY T.Dept_No;
RECURSIVE Derived Table Hierarchy
Above is a company hierarchy and this is what we will use to perform our WITH Recursive query.
RECURSIVE Derived Table Query
WITH TeraTom
(Emp, Mgr, LastN, Pos_Name, DEPTH) AS
(SELECT Employee_No, Mgr_Employee_No,
Last_Name, Position_Name, 0
FROM Hierarchy_Table
WHERE Mgr_Employee_No IS NULL
UNION ALL
SELECT Employee_No, Mgr_Employee_No,
Last_Name, Position_Name, DEPTH+1
FROM TeraTom
INNER JOIN
Hierarchy_Table
ON Emp= Mgr_Employee_No
) SELECT *
FROM TeraTom
ORDER BY 5,2,1 ;
Above is the WITH Recursive query.
RECURSIVE Derived Table Definition
Above is the WITH Recursive query and the highlighted part explains the recursive derived table definition itself.
WITH RECURSIVE Derived Table Seeding
Above is the WITH Recursive query and the highlighted part explains how the first row is placed inside the derived table. The only employee with no manager is the CEO, Tom Coffing. His Mgr_Employee_No is NULL. The table is now seeded!
WITH RECURSIVE Derived Table Looping
Above is the WITH Recursive query and the highlighted part explains how the derived table is joined to the Hierarchy_Table in a looping fashion. The highlighted part keeps looping and adding rows until it loops and adds no rows. Then it is done.
RECURSIVE Derived Table Looping in Slow Motion
Above is the WITH Recursive query and the highlighted part explains how the derived table is joined to the Hierarchy_Table in a looping fashion. The highlighted part keeps looping and adding rows until it loops and adds no rows. Then it is done. This is the first loop and as you can see two rows were added. That is because our join condition is Emp = Mgr_Employee_No. Both Stevens and Gonzales report to a manager with an Emp = 1.
RECURSIVE Derived Table Looping Continued
Above is the WITH Recursive query and the highlighted part explains how the derived table is joined to the Hierarchy_Table in a looping fashion. The highlighted part keeps looping and adding rows until it loops and adds no rows. Then it is done. This is the second loop and as you can see two rows were added. That is because our join condition is Emp = Mgr_Employee_No. Both Patel and Mumba report to a manager inside our recursive derived table.
RECURSIVE Derived Table Looping Continued
Six rows are added in the third loop.
RECURSIVE Derived Table Ends the Looping
No rows were added in the fourth loop. This loop is done!
RECURSIVE Derived Table Definition
Above is the WITH Recursive query and the highlighted part is now run so the final answer set can be delivered.
RECURSIVE Derived Table Answer Set
Using a Table Variable
Table Variables only exist for the duration of their batch, stored procedure or function.