Temporary table vs Table variable vs derived Table

Four options available for temporary matrix storage: Derived Table and Temporary Table are the oldest, followed by Table Variable and the latest is CTE which can also be recursive.




Temp tables,
          IO Operation - HIGH
          Explicit Indexing is allowed
          Constraints are allowed
          Need not create or declare, we can use the INTO clause to create this object.        
          Reusable across the batch but with current session
          Recommended when the data is huge & if there is any manipulation on the result
          Supports concurrency users
          Bit slower than table variables.


Table variables,
          IO Operation – LOW or NA
          Explicit Indexing not allowed – (Primary/Unique key can create index)
          Constraints are allowed
          Have to declare the variable
          Only used by the current batch scope
          Recommended when the data is less & if there is any manipulation on the result
          Supports concurrency users
Faster when the number of records is less


Derived tables,
          IO Operation – NA
          Declaration – NA
          Explicit Indexing – NA
          Constraints - NA
          Only on the current query(line)
          If it is not reusable data then we can go for it
          Recommended for inline quires
          Supports concurrency users