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 is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|