TEMPORARY TABLES IN SQL SERVER

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

1. LOCAL TEMPORARY TABLES
2. 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

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