|
Temp Tables :
In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-
Different Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
-
Local Temp Table
-
Global Temp Table
01.Local Temp Table:
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.
Example :
CREATE TABLE #LocalTemp
(
UserID int,
Name varchar(50),
Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Brijesh Kumar','Noida');
GO
Select * from #LocalTemp
The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
01.Global Temp Table:
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
Example :
CREATE TABLE ##GlobalTemp
(
UserID int,
Name varchar(50),
Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Brijesh Kumar','Noida');
GO
Select * from ##GlobalTemp
Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
Table Variables :
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.
Example
GO
DECLARE @TEmployee TABLE
(
Id INT IDENTITY(1,1),
EmpName VARCHAR(50),
Age INT
)
--Insert data to Table variable @TEmployee
INSERT INTO @TEmployee(EmpName , Age)
VALUES('BRIJESH KUMAR', 25)
--Select data
Select * from @TEmployee
--Next batch
GO
Select * from @TEmployee --gives error in next batch
CTE (Common Table Expressions) :
A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated everytime it's needed.
You might also consider here a table variable. This is used as a temp table is used, but is also in-memory only, but can be used multiple times without needing to be recreated every time. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this is a handy in-memory structure.
Example :
;WITH Emp([Eid], [Salary]) --Column names for CTE, which are optional
AS
(
SELECT TOP 3 [Eid], [Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC
)
--User CTE
SELECT TOP 1 [Eid], [Salary] FROM emp ORDER BY Salary ASC
Difference between CTE(Common Table Expressions) and Table Variable :
|