http://support.microsoft.com/kb/243586/

 Myth1 : table variables are created in memory

 well that’s false Table variables are created in Tempdb

lets prove:

1. create a temp table
2. Wait for 10 secs
3. create table variable
4. see all the objects in TempDB

DECLARE  @T TABLE (VAL DATETIME)

INSERT INTO @T (VAL) VALUES (GETDATE())

WAITFOR DELAY '00:00:10'

CREATE TABLE #T (VAL DATETIME )

INSERT INTO #T (VAL) VALUES (GETDATE())

SELECT * FROM TEMPDB.SYS.OBJECTS WHERE TYPE = 'U' ORDER BY CREATE_DATE DESC

MYTH 2 : Table variables can not have any indexes

well that’s false , once a table variable is created you can not use DDL statements against it  . However, while declaring the table we can declare PRIMARY KEY or UNIQUE constraint in the table definition.

                 DECLARE @T table (ID INT PRIMARY KEY CLUSTERED )

Similarities between temporary tables and table variable:

– Both are created in tempdb
– You can create constraint like primary key, default and check on both but the table variable has certain limitation for the default and check constrain where you can not use UDF
– Clustered indexes can be created on table variables and temporary tables
– Both are logged in the transaction log but the tempDB recovery model is SIMPLE, log will be truncated once the trasaction get complete.
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.
Differences
– You can not create non-cluster index and statistics on table variable but you can create it on temporary table.
– You can not use DDL statement on table variable but you can use it on temporary table.
– Table variable doesn’t support transaction wheras temporary table supports

1) SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan. Note that the estimated execution plan for both table variables and temporary tables will show 1 row; however the actual execution plan for temporary tables will change once SQL has recompiled the stored procedure . Also note that if a temporary table doesn’t exist, you will get an “Invalid object name ‘<tablename>’” error when creating the estimated execution plan.

2) As mentioned previously, you cannot perform any DDL statements against a table variable. For instance, you might have a need to populate a table, and then add an index or column. In this case, you will need to use a temporary table.

3) With table variables, you cannot select into a table variable using the SELECT <columnlist> INTO <table> syntax. As demonstrated in the code above, you can do this with a temporary table.

4) With SQL 2008, you can pass a table variable to a procedure if you have defined a user-defined table type and this is the type for the parameter. You cannot do this with temporary tables, or with table variables in SQL 2000 or SQL 2005 .

5) Scope: table variables are only visible while the current batch of statements is running, and they are not visible to any nested procedures. Local temporary tables are visible to the current session once created, including nested procedures; however they will not be visible to parent procedures. Global temporary tables are visible to all sessions until dropped and all other sessions have stopped using them.

6) Collation: table variables use the collation of the current database. Temporary tables use the collation of the tempdb database. If they are not compatible, then you will need to specify the collation to use in either the queries or the table definition.

7) If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.

So, what should I use?

Microsoft recommends using table variables (in Reference 4). If the number of rows that you are going to be inserting into the table is very small, then use a table variable. Most of the “internet gurus” will tell you to have less than 100 records as a guideline, as this is where the lack of statistics start to mess up a query – but they will also tell you to test your specific needs against both methods. Some people will only use table variables within user-defined table functions (which require them). If you can use an index from either a PRIMARY KEY or UNIQUE constraint on a table variable, then you could get excellent performance from table variables that contain tens of thousands of records. This is primarily true if you don’t have to join the table variable to another table. When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is.

To illustrate this, see the attachment to this article. It creates both a temporary table and a table variable, and populates each of these tables with the contents of the AdventureWorks.Sales.SalesOrderDetail table. In order to get a large data size (> 1 million rows), I perform 10 inserts on each table, with each insert being in a different order. I then perform an identical query on each table, joining up against the AdventureWorks.Sales.SalesOrderHeader table and filter by the ModifiedDate field. If you examine the output statistics, you will see a remarkable difference in the number of logical reads on the joined table between the select with the temporary table versus the select with the table variable. The same data, running the same query, is producing very different performance results. Time wise (on my system) it consistently takes the table variable over 50 seconds, while the temporary table takes under 8 seconds.

If your process requires you to perform DDL statements on the table after creation, then you will need to use a temporary table.

Temp Tables and Table Variables have overlapping purposes, so there’s no real concrete answer to which you should use. For any given situation, you’ll find the optimum only by examining the advantages, disadvantages, and characteristics of each and by doing some simple performance testing. The side-by-side comparison in the following “Summary” section should make the first part of that a bit easier to accomplish.

Summary

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement.SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types.Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC.SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.

Note : Test results may vary depending up on the environment.

here’s the code

/***** AUTHOR : AKHIL
       TEST DATE : 11/5/2011
       ********************/

BEGIN TRAN 

DECLARE @MAXCNT INT = 100
DECLARE @MINCNT INT = 1
DECLARE @STTIME DATETIME = GETDATE() 

CREATE TABLE #T(ID INT  , VAL VARCHAR(100))

WHILE @MINCNT < = @MAXCNT 

BEGIN

    INSERT INTO #T (ID)
    SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY NAME)   

    FROM SYS.ALL_OBJECTS

    UPDATE #T SET VAL = 'X'

    SET @MINCNT = @MINCNT +1

    DELETE #T
END 

COMMIT TRAN

SELECT DATEDIFF (MS,@STTIME,GETDATE()) AS 'DURATION FOR LOCAL TEMPORARY TABLE'

BEGIN TRAN 

SET @MAXCNT  = 100
SET @MINCNT = 1
SET @STTIME = GETDATE() 

CREATE TABLE ##T(ID INT, VAL VARCHAR(100))

WHILE @MINCNT < = @MAXCNT 

BEGIN

    INSERT INTO ##T (ID)
    SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY NAME)   

    FROM SYS.ALL_OBJECTS

    UPDATE ##T SET VAL = 'X'

    SET @MINCNT = @MINCNT +1

    DELETE ##T
END 

COMMIT TRAN

SELECT DATEDIFF (MS,@STTIME,GETDATE()) AS  'DURATION FOR GLOBAL TEMPORARY TABLE'

BEGIN TRAN 

SET @MAXCNT  = 100
SET @MINCNT  = 1
SET @STTIME  = GETDATE() 

DECLARE @T TABLE (ID INT , VAL VARCHAR(100))

WHILE @MINCNT < = @MAXCNT 

BEGIN

    INSERT INTO @T (ID)
    SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY NAME)   

    FROM SYS.ALL_OBJECTS

    UPDATE @T SET VAL = 'X'

    SET @MINCNT = @MINCNT +1

    DELETE @T
END 

COMMIT TRAN
SELECT DATEDIFF (MS,@STTIME,GETDATE()) AS 'DURATION FOR TABLE VARIABLES'

Thank you
Any questions please comment
note : some of the contents in this post are referenced from multiple blogs (sqlserverperformance, sqlservercentral,sqldbpool)