Temporary Tables
There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.
CREATE TABLE #MyTempTable
(
PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
LastName VARCHAR(50) NOT NULL
)
INSERT INTO #MyTempTable
SELECT PolicyId, LastName
FROM dbo.Policy
WHERE LastName LIKE 'A%'
Table Variables
Existing in memory for the duration of a single T-SQL batch, table variables are declared using syntax similar to local variable declaration.
DECLARE @MyTableVariable TABLE
(
PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
LastName VARCHAR(50) NOT NULL
)
Derived Tables
Derived Tables are similar to table variables in that they exist in memory only but they differ in how they are created and used. A derived table is created when a SELECT query is given a name and joined to from another table. Performance benefits of using a derived table can be quite substantial in many situations and should be considered whenever possible. Derived tables exist only for the duration of a single T-SQL statement and although they can be referenced multiple times (through joins and predicates), they cannot be referenced outside the T-SQL statement in which they are created.
Example:
Let's assume we have a table called PolicyVehicle that contains one record for each vehicle on an auto insurance policy with a reference back to the Policy table via the PolicyId foreign key column. Although we could use a simple query to get the newest vehicle and associated policy information, for the sake of this example we will use a derived table to first retrieve the newest Vehicle for each Policy, alias that derived table with the name 'NewestVehicle', and then join to the Policy table to retrieve the PolicyId and LastName columns.
SELECT P.PolicyId, LastName, NewestVehicle
FROM Policy P
INNER JOIN
(
SELECT PolicyId,
MAX(VehicleYear) AS NewestVehicle
FROM PolicyVehicle PV
GROUP BY PolicyId
) MaxVehicles ON P.PolicyId = MaxVehicles.PolicyId
Deciding which type of table to use
Things to be aware of when using temp tables versus table variables versus derived tables:
* Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
* Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.
* You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.
* Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
* In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.