Temporary Tables in SQL Server (Temp Tables)

   Part - 9

Click here or below for you tube Answer

 → Temporary tables or Temp tables works same as physical tables, they are created in Tempdb which is one of the system databases of SQL Server

 → Below are the types of Temp tables in SQL Server 
      → Local Temp Tables start with # 

      → Global Temp Tables starts with ##

 → Scope of Local Temp tables are limited to its session in which it is created

 → However Global Temp tables can be seen in all other sessions across the connection and it will be dropped automatically when the created session is closed 

 → Creation / Dropping of Temp Tables is same as Physical Tables except that they should be prefixed with # or ## signs based on the type of Temp Table you are creating

 → Max Length of Local Temp Table is 116 Characters

  → Main use of using temp table is that they to cause minimal locking as well as less logging

  → Views are Functions can not be created on top of Temp Tables, However Indexes can be created

  → The indexes on Temp tables makes use of Statistics gives better query plan compared to Table variables

Lets demonstrate the above discussed points using the below SQL scripts

------------> Creation of Temp Tables is same as Physical tables except prefixing # or ## sign

---> Local Temp Table: 

-- Local Temp table with same name can be created in different sessions

-- These are differentiated with Session_IDs

CREATE TABLE #Employee (EmpID INT)  

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES

#Employee___________________________________________________________________________________________________________000000000027

INSERT INTO #Employee VALUES (1),(2)

SELECT * FROM #Employee

 -- DROP TABLE  #Employee

--> Global Temp Table: 

-- Global Temp table with same name can not be created in different sessions

CREATE TABLE ##Employee (EmpID INT)  

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES

Insert ##Employee12 Values (1),(2), (3)

These values can be seen in another session as well, as the Global Temp tables are accessible across the sessions.

 -- DROP TABLE  ##Employee

------------> Views/Functions Can not be created on top of Temp table

CREATE VIEW Employee_Temp

AS 

SELECT * FROM ##Employee

------------> Indexes can be created on top of temp tables

CREATE INDEX IDX_TempEmployee_Id on #Employee (Empid)

SELECT * FROM tempdb.INFORMATION_SCHEMA.INDEXES

select * from #Employee --> (Check the execution plan)

----------->  Temp Tables are created in Temp DB, Ignores any other database context

USE SQLwithRavimartha

GO

create table ##Employee12 (Id int)

Insert SQLwithRavimartha.dbo.##Employee12 Values (1),(2), (3)

----------->  Max Lenghth of temp table can be 116 characters

-- Local Temp

create table 

#testinggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg 

(id int)

SELECT LEN('testinggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg')

-- Global Temp

create table 

##testingggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg 

(id int)

SELECT LEN('testingggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg')

SELECT * FROM ##testingggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg