SQL temp table vs table variable

There are some differences between “Temporary Tables” (#tempTable) and “Table Variables” (@tempTable).

Point 1:

A Temp table (#tmp) can do all the DDL operations and it allows creating the indexes, altering and dropping.

A Table variable (@tmp) is not allowed doing the DDL operations but can create the clustered index only.

Point 2:

A Temp table (#tmp) is easy to create and back up your data.

A Variable table (@tmp) is easy to create but involves the extra effort for create the normal tables and then back up your data.

Point 3:

A Temp table (#tmp) result can be used by multiple users.

A Variable table (@tmp) result can be used by the current user only.

Point 4:

A Temp table (#tmp) will be stored in the tempdb and create network traffic. If we have large amount of data in the temp table and it will create performance issue.

A Table variable (@tmp) will be store in the physical memory for some of the data, and then later when the size increases it will be moved to the tempdb.

Point 5:

A Temp table (#tmp) can be used for the current session or global, so that multiple user session can utilize the results in the table.

A Table variable (@tmp) can be used inside program, function or procedures.

Point 6:

In the Temp table (#tmp), function not allows us to use the Temp table.

In the Table variable (@tmp), function allows us to use the table variable.

Example 1:  @ Table variable

--CREATING TABLE VARIABLE.
DECLARE @Email_Table TABLE(SNo INT IDENTITY(1, 1), EmailAddress nvarchar(max), CustomerID NVARCHAR(55), ProductId nvarchar(max))

--INSERT INTO TABLE VARIABLE.
INSERT INTO @Email_Table
    SELECT Q.EmailAddress, Q.CustomerId, P.Id
            FROM [dbo].[EmailQue] AS Q 
                INNER JOIN Detail AS WOD On WOD.Id= Q.WorkOrderDetailId
                INNER JOIN ProductDetail as P on P.Id=WOD.ProductId
            WHERE Q.EmailSent = 0         
    GROUP BY  EmailAddress, CustomerId, P.Id

--SELECT INTO TABLE VARIABLE.
SELECT EmailAddress,CustomerID, ProductId FROM @Email_Table