Difference between Table Variables, Temporary Tables (Hash Tables) and Temporal Tables

Table Variables

• Table variable is a special data type that can be used to store a result set for processing at a later time. 
• Table variable is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. 
• Functions and variables can be declared to be of type table. 
• Table variables can be used in functions, stored procedures, and batches. 
• To declare variables of type table, use DECLARE @local_variable
• A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

Example:

DECLARE @myTable TABLE

(

       Id INT PRIMARY KEY,

       FirstName VARCHAR(100) UNIQUE,

       LastName VARCHAR(100) NOT NULL

)

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (2,'B',NULL) --Fail

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (3,'A','A') --Fail

SELECT * from @myTable

SELECT * INTO @This_Will_Not_Work FROM @myTable

Only constraints allowed on the table variables are:

1. Primary Key
2. UNIQUE
3. NULL

Further, SELECT * INTO will not work

Temporary Tables (Hash Tables)

There are two types of temporary tables; one is local and the other is global.

Local Temporary Tables 

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows.

Example:

--Local temporary table

CREATE TABLE #myLocalTable

(

       Id INT PRIMARY KEY,

       FirstName VARCHAR(100) UNIQUE,

       LastName VARCHAR(100) NOT NULL DEFAULT('')

)

INSERT INTO #myLocalTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO #myLocalTable(Id, FirstName) VALUES (2,'B') --Default LastName

Global Temporary Tables  

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name.

Example:

--Global temporary table

CREATE TABLE ##myLocalTable

(

       Id INT PRIMARY KEY,

       FirstName VARCHAR(100) UNIQUE,

       LastName VARCHAR(100) NOT NULL DEFAULT('')

)

INSERT INTO ##myLocalTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO ##myLocalTable(Id, FirstName) VALUES (2,'B') --Default LastName

Temporal Tables

SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

Example:

CREATE TABLE dbo.TemporalTable

(

       ID INT NOT NULL PRIMARY KEY CLUSTERED,

       FirstName VARCHAR(100),

       LastName VARCHAR(100),

       ValidFrom datetime2 GENERATED ALWAYS AS ROW START,

       ValidTo datetime2 GENERATED ALWAYS AS ROW END,

       PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory));

--Insert a record

INSERT INTO dbo.TemporalTable(ID, FirstName, LastName) VALUES(1,'Akash','Agrawal')

--Current table will have one record. Notice the ValidFrom and ValidTo columns

SELECT * FROM dbo.TemporalTable

SELECT * FROM dbo.TemporalTableHistory --zero records

--Now, update record in the table

UPDATE TemporalTable

SET FirstName = 'Aakash'

WHERE ID = 1

--Data will be updated in current table. Notice the ValidFrom and ValidTo columns

SELECT * FROM dbo.TemporalTable

--History table will have one record with ValidFrom and ValidTo columns populated.

SELECT * FROM dbo.TemporalTableHistory

--Update again

UPDATE TemporalTable

SET FirstName = 'Aakaash'

WHERE ID = 1

--Notice multiple rows in History table

SELECT * FROM dbo.TemporalTable

SELECT * FROM dbo.TemporalTableHistory

--How to query History data

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME BETWEEN '2018-01-22 08:12:26.2254807' AND '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME AS OF '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME FROM '2018-01-22 08:12:26.2254807' TO '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME CONTAINED IN('2018-01-22 08:12:26.2254807' , '2018-01-22 08:15:44.7907589')

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME ALL

ORDER BY ValidFrom