What's the benefit of doing temporary tables (#table) instead of persistent tables (table)?

I can think of two main benefits:

1. Avoiding concurrency problems, if you have many processes creating/dropping tables you can get in trouble as one process tries to create an already existing table.
2. Performance, I imagine that creating temporary tables (with #) is more performant than regular tables.

Is there any other reason, and is开发者_开发百科 any of my reasons false?

---

You can't compare temporary and persistent tables:

• Persistent tables keep your data and can be used by any process.
• Temporary ones are throw away and #ones are visible only to that connection

You'd use a temp table to spool results for further processing and such.

There is little difference in performance (either way) between the two types of table.

You shouldn't be dropping and creating tables all the time... any app that relies on this is doing something wrong, not least way too many SQL calls.

---

(1)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.

(2)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.

(3)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.

(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.

(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

---

1) A table variable's lifespan is only for the duration of the transaction that it runs in. If we execute the DECLARE statement first, then attempt to insert records into the @temp table variable we receive the error because the table variable has passed out of existence. The results are the same if we declare and insert records into @temp in one transaction and then attempt to query the table. If you notice, we need to execute a DROP TABLE statement against #temp. This is because the table persists until the session ends or until the table is dropped.

2) table variables have certain clear limitations.

-Table variables can not have Non-Clustered Indexes -You can not create constraints in table variables -You can not create default values on table variable columns -Statistics can not be created against table variables -Similarities with temporary tables include:

Similarities with temporary tables include:

-Instantiated in tempdb -Clustered indexes can be created on table variables and temporary tables -Both are logged in the transaction log -Just as with temp and regular tables, users can perform all Data Modification Language -(DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.