Temporary table vs Table variable vs derived Table

Question

I would like to know from the experts

1)when we should use a Temporary table, a Table variable and a Derived table ?

2)What are the limitations and advantages of each over the others?

Answer 1

This is not full info but i given as much as i covered,

 

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

 

 Note: In SQL Server 2005, one more feature is added viz. CTE - Common Table Expression. it has a its own advantages. To know more about CTE see on the books online.

 

Answer 2

A couple of other considerations are:

• For very large queries temp tables might perform a bit better
• Table variabes cannot be used as the target for INSERT ... EXEC syntax (SQL 2000 and older)
• Temp tables cannot be used inside of UDFs
• Table variables cannot participate in parallel processing where temp tables can

Answer 3

I will often experiment with 2 or 3, and use the one that provides the best query plan and execution speed.

 

I have seen using table variables radically increase the speed of a query as opposed to derived tables or multiple table complex JOINs.

 

And vice versa.

 

They are all tools, use the best tool for the task at hand.

 

The major differences have to do with scope and persistance.

Answer 4

One important thing to keep in mind, is that SQL Server does not store statistics for table variables, so the query processor can not count on statistics to estimate cardinality.

 

INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

http://support.microsoft.com/?kbid=305977

 

 

AMB

Answer 5

Hi Kent,

Can you Tell me why Table variable is unable to process very fast when we have large data..?

Answer 6

Hi Kent,

Can you Tell me why Table variable is unable to process very fast when we have large data..?

Answer 7

Hi Kent,

Can you Tell me why Table variable is unable to process very fast when we have large data..?

As Hunchback noted, SQL does not keep statistics on table variables.  And, as Manivannan noted, you have limited options for building indexes on table variables.  With small tables, indexes and statistics are often little or no help.  But with large tables, indexes and statistics can aloow the query optimizer to create a much more efficient plan.

And as Kent said, for large tables, temp tables MIGHT (emphasis mine) perform a bit better.  Whether it does or not depend on the table DDL, the amount and distribution of the data and the queries you do against the table.  Since creating indexes and statistics have a cost, if SQL is not able to make efficient use of them, their cost can exceed their benefit.

As a general rule, my experience has been that for small amounts of data, table variables do slightly better.  And for large tables, temp tables do better.  But there are lots of exceptions.

Tom