Temp Table Vs Temp Variable

Temp table is valid for a session (Connection).
Defination :
Create table #temp (Name as Varchar(100))
Insert into #temp (Name) Value ('Switin');

It can be used in Into Statement
Select * Into #Temp From SomeTable

Go
1.If you try to create one more Temp Table with Same Name with same Connection Exceptation will be raised
2.Best Part you can create Primary Key, Indexes, Constraints. As we do for normal tables
3.As soon as connection is dead the Temp Table is Dropped automatically by SQL SERVER . Cool Part

Temp Variable are Tables but for the Scope. They are Scope driven
Defination :
Declare @temp table(Name Varchar(100))
Insert into @temp (Name) Value ('Switin');

Go
Declare @temp table(Name Varchar(100))
Insert into @temp (Name) Value ('Switin');

Above mentioned syntax will not generate any error as after first creation of table scope of variable end when Go is Called. Only Weak point in Table Variable is SELECT INTO statment cannot be used
Mostly used in SP, Triggers and Table Valued Functions.


Regards
Switin Kotian

Comments

Popular posts from this blog

Shared / Static Class in vb.net/C#

Xamarin Forms : PopUp Page with RG.Plugins.Popup

Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)