Temporary Tables In SQL
TEMPORARY TABLES IN SQL SERVER
LOCAL TEMPORARY TABLES IN SQL
GLOBAL TEMPORARY TABLES IN SQL

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

LOCAL TEMPORARY TABLESGLOBAL 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 connectionsIn 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

Leave a Reply

Your email address will not be published. Required fields are marked *