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');
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
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
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
Post a Comment