Temporary Tables vs. Table Variables

There are three major theoretical differences between temporary tables:

create table #T (…)

And table variables:

declare @T table (…)

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T

s
---------------
old value #

s
---------------
new value @

After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

For more Details please Visit:http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Temporary tables in SQL Server

Introduction

Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. There are two types of temporary table in SQL Server, local and global.

Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed.

Both types of temporary tables are created in the system database tempdb.

Creating Temporary Tables

Temporary tables can be created like any table in SQL Server with a CREATE TABLE or SELECT..INTO statement. To make the table a local temporary table, you simply prefix the name with a (#). To make the table a global temporary table, prefix it with (##).

Example
-- Create a local temporary table
CREATE TABLE #LocalTempTable
(
Field1 INT,
Field2 VARCHAR(20)
)

CREATE TABLE ##GlobalTempTable
(
Field1 INT,
Field2 VARCHAR(20)
)
* you can do all the DML operation on Temporary table.

Derived Table in SQL SERVER

Introduction
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query. In very basic terms, a derived table is a virtual table that's calculated on the fly from a select statement.

In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step.

Boost Performance
The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.

Here are the steps when you use a temporary table:

Lock tempdb database
-CREATE the temporary table (write activity)
-SELECT data & INSERT data (read & write activity)
-SELECT data from temporary table and permanent table(s) (read activity)
-DROP TABLE (write activity)
-Release the locks
Compare this with the number of steps it takes for a derived table:

-CREATE locks, unless isolation level of "read uncommitted" is used
-SELECT data (read activity)
-Release the locks
As is rather obvious from this example, using derived tables instead of temporary tables reduces disk I/O and can boost performance.


example

without derived table
SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID
WHERE YEAR(O.OrderDate) = 1996
GROUP BY C.CustomerID, C.CompanyName

with derived table
SELECT C.CustomerID, C.CompanyName,
COUNT(dOrders.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN
/* start our derived table */
(SELECT * FROM Orders WHERE YEAR(Orders.OrderDate) = 1996) AS dOrders
/* end our derived table */
ON
C.CustomerID = dOrders.CustomerID
GROUP BY C.CustomerID, C.CompanyName

Updating a derived table
Now the question of updating a derived table. I don't think that it is a big issue, but still it's a question asked about derived tables. So the answer is as follows,

- MS SQL 2000 - Not possible

- MS SQL 2005 - Possible

So, updating a derived table is not possible in SQL Server 2000. If you try to do so, then SQL Server will give you the following error.

Update T SET Id=0 FROM (SELECT * FROM tt1) AS T
Error:
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'T' is not updatable because a column of the derived table is derived or constant.

To Know more please visit: http://www.sqlservercentral.com/articles/DerivedTables/61388/