python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
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
DECLARE @Email_Table TABLE(SNo INT IDENTITY(1, 1), EmailAddress nvarchar(max), CustomerID NVARCHAR(55), ProductId nvarchar(max))

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 EmailAddress,CustomerID, ProductId FROM @Email_Table


Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - and
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like Powered by Blogger.
ASK Questions