2nd-3rd-4th-nth highest salary

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

ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Buy Books Online at Best Prices

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.