temp table, table variable, CTE (Common Table Expression)

Rule of thumb: If logic is simple CTE, otherwise Table Variable

CTE (Common Table Expression):
* May have performance issues
* Like a sub-query
* Save you some type and make the field type consistent with the original table
* Saved in memory

Table Variable:
* Performance better
* Saved in database

Temp Table:
* Do you best to not use it

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Temp Table

CREATE TABLE #MyTable
(
Id int,
Name varchar(32)
)

DROP TABLE #MyTable

alternative syntax
select * into #MyTable from contacts
drop table #MyTable
-----------------------

Table Variable

DECLARE @MyTable TABLE
(
Id int,
Name varchar(32)
)

----------------------

CTE (Common Table Expression)
WITH MyTable (Id, Name)
AS
(
SELECT Id, Name
FROM User
)