TEMPORARY TABLES IN SQL SERVER
- In SQL Server, Temporary tables, are very similar to the permanent tables.
- In SQL Server, A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server.
- In SQL Server, Temporary Tables are Created in TempDB.
- In SQL Server, TempDB resides in System Databases in SSMS.
- In SQL Server, Temporary tables are very useful when we need to store temporary data.
TYPES OF TEMPORARY TABLES
- LOCAL TEMPORARY TABLES
- GLOBAL TEMPORARY TABLES
SYNTAX OF LOCAL TEMPORARY TABLE
CREATE TABLE #EmpDetails
(
id INT,
name VARCHAR(25)
)
LOCAL TEMPORARY TABLES
- A local temporary table is available, only for the connection that has created the table.
- In SQL Server, A local temporary table is automatically dropped, when the connection is closed.
- In SQL Server, We have Single # Symbol before the name of local temporary table.
- In SQL Server, We can explicitly drop the temporary table by using DROP TABLE #TableName.
- In SQL Server, If Temporary Table is created inside the SP (Stored Procedure) then it gets dropped automatically when Stored Procedure Completes its execution.
- In SQL Server, We can create the local temporary table with same name in different connections.
GLOBAL TEMPORARY TABLES IN SQL SERVER
- In SQL Server, To create the global temporary table, use 2 (##) hash symbols before the name of the table.
- In SQL Server, Global temporary tables are accessible or visible in all the connections.
- In SQL Server, Global temporary tables destroyed when the last connection referencing the table is closed.
- In SQL Server, If you close the connection that has created the global temporary table then that global temporary table will automatically deleted.
- In SQL Server, We can create the local temporary table with same name in different connections But a global temporary table name has to be unique it means we cannot create the global temporary table with same name in different connections.
- In the object explorer, there will be no random numbers suffixed at the end of the table name.
- In SQL Server, Local and global Temporary Tables are Created in TempDB.
DIFFERENCE BETWEEN LOCAL TEMPORARY TABLES & GLOBAL TEMPORARY TABLES IN SQL SERVER
LOCAL TEMPORARY TABLES | GLOBAL TEMPORARY TABLES |
---|---|
In SQL Server, We have Single # Symbol before the name of local temporary table. | In SQL Server, To create the global temporary table, use 2 (##) hash symbols before the name of the table. |
SQL Server appends some random numbers at the end of the local temp table. | SQL Server did not appends random numbers at the end of the global temp table. |
In SQL Server, Local temporary tables are only visible to that connection / session of the SQL server which has created it. | In SQL Server, Global temp tables are visible to all SQL Server connections / sessions. |
In SQL Server, We can create the local temporary table with same name in different connections | In SQL Server, We cannot create the global temporary table with same name in different connections. |
SIMILARITIES
- Both are created in TempDb in system databases.
- Both are automatically deleted when the connection is closed.
- Both are the types of Temporary tables.
SQL Query For Local Temporary Tables
create table #EmpData
(
name varchar(50),
gender varchar(50)
);
insert into #EmpData values ('Adil','Male');
insert into #EmpData values ('Anum','Female');
insert into #EmpData values ('Amir','Male');
select * from #EmpData;
select name from tempdb..sysobjects
where name like '%EmpData%';
exec spEmpData;
create procedure spEmpData
as
begin
create table #EmpData
(
name varchar(50),
gender varchar(50)
);
insert into #EmpData values ('Adil','Male');
insert into #EmpData values ('Anum','Female');
insert into #EmpData values ('Amir','Male');
select * from #EmpData;
end
Download Notes Of This Blog, From The Link Given Below.
https://www.mediafire.com/file/82eiprgt3ctyrsq/TEMPORARY+TABLES+IN+SQL+SERVER.pptx/file
No responses yet