l want to take on three topics in this tutorial. That is temporary tables, derived tables and table variables. l want to take on all of them at once so l can compare and discuss the technical implementation of all of them at the same time. l hope you understand what a table is, in any database. If you are reading this tutorial without first understanding what a table is and how to create and manipulate regular tables in any database, then l’ll advise you to learn what a tables is first. You can find tutorial on that here.
Lets start with Temporary tables. A temporary table is just like a regular table but the scope and the storage place of temporary table differs. When you create a regular table, it is stored in the database in which you created it, on the other hand, a temporary table is stored in a special database in sql server called tempdb database. There are two types of temporary tables. These are local and global temporary tables. The difference between a local and global temporary table is only in the scope of the tables. Local tables are only visible or available within the session in which they are created. Global temporary tables are available in the session that creates them and any other session while the session that created it is still active. When the session that created either a local or global table is ended, the table is dropped from the tempdb database. This is the most important difference between regular tables and temporary tables. Regular tables permanently persist in the database even if the session is closed or the server is restarted but temporary tables do not. You can perform any action on temporary tables just like regular tables.
Temporary tables are created using the CREATE TABLE statement in TSQL just like creating regular tables. To create a local temporary table, add the # sign infront of the table name, to create a global temporary table, add ## infront of the table name. lets see an example.
————-Local temporary table creation——————————————-
CREATE TABLE #localtable( id int, name varchar(50))
———–Put data into the local temporary table——————————————–
INSERT INTO #localtable
VALUES(1,’John’),(2,’Cosby’)
This can only be done in the session that created this table
———-read from the local temporary table ——————————————-
SELECT *
FROM #localtable
This can only be done in the session that created this table
——————————Global temporary table creation—————————-
CREATE TABLE ##globaltable(id int, name varchar(50))
—————————-Put data into the table—————————————–
INSERT INTO ##globaltable
VALUES (1,’John’),(2,’Cosby’)
This can be preformed in any session as far as the session that created the table is still active.
——————————-Read data from global temporary table————————
SELECT *
FROM ##globaltable
This can be preformed in any session as far as the session that created the table is still active.
—————————————————————————————————————-
Now let’s move on to Derived tables.
Derived tables are simply select statements that are given alias name and can be used to perform tsql operations such as joins, aggregations just like regular tables. Derived tables are not stored in the database, they are virtual tables and are query bound. That means, the derived table is only visible in the query that defined it, it doesn’t exist outside of that query statement.
To create a derived table, you’ll go about doing your regular select statement then put the entire select statement into closing brackets and give a name to it, like this
Lets say we have 3 tables, employee, customer and contact table.
CREATE TABLE Employee(Empid int ,ContactID int, HireDate Date, Dob Date)
CREATE TABLE Contact(ContactID int, Firstname varchar(50), Lastname varchar(50))
CREATE TABLE Customers(CustomerID int, ContactID int)
I used the same table in earlier tutorial, it should make sense. the main idea is, the contact table contains contact information for employees and customers.
Lets try to find the name of employees only. We also want to output the employeeid.
SELECT D.Empid, c.Firstname, c.lastname
From Contact c
INNER JOIN
( SELECT EmpID, ContactID
FROM Employees) as D
on D.Contactid = c. Contactid
The derived table is the select statement put in brackets and given an alias as D. You can call it whatever you want. I used that derived table and joined it to the Contact table on the ContactID column. That is the end of the visibility of derived table D. If You start another query, you will not be able to access it but you can keep joining other tables to it in the first query. You can’t start a separate query and still access D. The advantage of derived tables is that, you don’t need to worry about storing them. They don’t consume storage in the database. You can not however do an insert, delete, update on derived tables because the data does not actually get stored in it. If you need to store the data, but do not want it to exist after the session, then go for temporary tables.
The last thing to talk about is table variables. I included this topic here because l want to clear all about different types of tables once and for all. As the name sounds, table variables are variables. They are not objects that are stored in the database. If you have problem with what a variable is, then check out the tutorial l wrote on variables on here. They are just like any other variable, they are declared and used to perform tasks. They are memory containers and can assume dynamic data depending on what data is stored in them at a time. After execution of the code, the memory location where they were declared is deallocated to avoid memory leak. Table variables store 2D data. That is, they have rows and Columns. You can insert, update and delete data from the table variable. Table variables are batch bound, that means they are visible in the entire batch after they are declared. You can not perform DDL statements on table variables. Always remember, they are not stored in the database, all operations on the table variable and any other type of variable you ever declare happens in memory. Let’s look at an example. You declare, not create.
DECLARE @mytable TABLE(id int, name varchar(20))
INSERT INTO @mytable
VALUES(1,’Cosby’),(2,’Vince’)
UPDATE @mytable
SET name = ‘Kofi’
WHERE id = 1
SELECT *
FROM @mytable
———————–All these code can be run as a batch to see the result–
if you run part of the code without including the declaration, you’ll get error. You can perform join, aggregation etc on table variables as well. Just think of table variables as a table that is in memory, not in the database, that you can use for all DML operations.
Hope this helps someone. Got questions or suggestions? please drop a comment.
I’ll be discussing stored proceducres in the next tutorial, keep in touch. Peace.
