SQL - Temp Table (Local & Global Temp Tables)

Temporary table is also known as temp table. This is another most usable feature of the SQL Server. Temp tables are used to store temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW. 
Temp tables have the same feature as any normal data table. We can create indexes on the temp table but it depends on the requirements.

Whenever we create a temporary table, it goes to Temporary folder of tempdb database. Based on the behavior and scope of requirement, SQL server provides two types of temp tables as given below - 

1. Local Temp Table , 
2. Global Temp Table

Local Temp Tables – They are easily accessible for single user. This is the most usable in SQL server and works only in the current connection of the user. They automatically deleted from the session whenever user disconnects from instances. We can create them by starting with hash (#) sign as given below -

1. We can create them as we create permanent data tables in the database before use them and
2. We can create them by using into temp table name from the permanent table or views in the select statements. They get the same data structure from the select data columns which is very useful to avoid data conflict.

----- syntax to create temp table create table #TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into temp table Insert into #TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the temp table select EmpId, EmpName, EmpAge, EmpDept From #TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031 ----- get filtered result into another temp table select EmpId,EmpName, EmpAge,EmpDept into #Empbelow22 From #TempEmp where EmpAge>22 ----- pull the data from select EmpId, EmpName, EmpAge, EmpDept from #Empbelow22 EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 ----- drop temp tables drop table #Empbelow22, #TempEmp

Global Temp Tables –They are easily accessible for multiple users. The scope of Global temporary table is the same for the entire user for a particular connection. They automatically deleted whenever all users sessions are disconnected. We can create them by starting with double hash (##) sign. We cannot set revoke and grant permissions to the global temp table because it’s always accessible to all users.

----- syntax to create Global temp table create table ##TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into Global temp table Insert into ##TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the Global temp table select EmpId, EmpName, EmpAge, EmpDept From ##TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031

When can we use of temp tables –

There are lots of scenarios, where we can easily use of the temp table to increase the performances and solve the problems such as –

• In the stored procedure, wherever we require a large number of row manipulations. 
• They could be the replacement of the cursor in the query to store the result set data into a temp table, then we can manipulate the data from there. 
• They are the best option to do complex join operations.
• We can use of n-numbers of temp tables to do more complex data manipulation and call them in the entire single session.

Point to remember: To avoid additional overhead and performance issues, drop the temp tables after use of the temp table because Temporary table stored on tempdb of SQL Server which is a separate database.
To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable

SQL - FOR XML Commands

As we are well aware that SQL Server supports an enhancement to the T-SQL syntax that enables normal relational queries to output their result set as XML, using any of these four approaches:
1)  FOR XML RAW
2) FOR XML AUTO
3) FOR XML EXPLICIT
4) FOR XML PATH

The first three of these options were introduced with the very first XML support in SQL Server 2000. We’ll start with these options and then cover later XML enhancements added in SQL Server 2008, which includes the fourth option (FOR XML PATH).

Most common usable XML command is FOR XML PATH. Mostly, everyone has been worked with this command.

To understand the each FOR XML command, we will amuse that we have a Employee Master table as given below:

Employee Master
Emp Id	Employee Name	EmployeeDept	EmployeeRatePerDay
E0001	Ryan Arjun	Database	240
E0002	Tony Towery	Testing	230
E0003	Lucy Gray	IT	250
E0004	Will Smith	Database	245
E0005	Chao Milk	Testing	225
E0006	Chris Gyal	IT	210
E0007	Bill Gray	Database	190
E0008	Red Bill	Testing	210
E0009	Tom Ramsay	IT	200

FOR XML RAW Command
FOR XML RAW produces attribute-based XML. FOR XML RAW command essentially creates a flat representation of the data in which each row returned becomes an element and the returned columns become the attributes of each element. This is also does not interpret joins in any special way.

Example - Using FOR XML RAW to produce flat, attribute-based XML

SELECT [Emp Id] as EmpId       ,[Employee Name] as EmployeeName       ,[Dept Id] as Deptid       ,[EmployeeRatePerDay]   FROM [dbo].[EmployeeMaster]   order by [Employee Name]   FOR XML RAW

After execution of the above query, we will get the query results as a hyperlink and after clicking on that hyperlink, we will see the output rendered as properly formatted XML in a color-coded window that supports expanding and collapsing nodes as shown below -

<row EmpId="E0007" EmployeeName="Bill Gray" Deptid="D003" EmployeeRatePerDay="190.00" /> <row EmpId="E0005" EmployeeName="Chao Milk" Deptid="D002" EmployeeRatePerDay="225.00" /> <row EmpId="E0006" EmployeeName="Chris Gyal" Deptid="D002" EmployeeRatePerDay="210.00" /> <row EmpId="E0003" EmployeeName="Lucy Gray" Deptid="D001" EmployeeRatePerDay="250.00" /> <row EmpId="E0008" EmployeeName="Red Bill" Deptid="D003" EmployeeRatePerDay="210.00" /> <row EmpId="E0001" EmployeeName="Ryan Arjun" Deptid="D001" EmployeeRatePerDay="240.00" /> <row EmpId="E0009" EmployeeName="Tom Ramsay" Deptid="D003" EmployeeRatePerDay="200.00" /> <row EmpId="E0002" EmployeeName="Tony Towery" Deptid="D001" EmployeeRatePerDay="230.00" /> <row EmpId="E0004" EmployeeName="Will Smith" Deptid="D002" EmployeeRatePerDay="245.00" />

FOR XML AUTO- This is also produces attribute-based XML (By default Nature) but its output is hierarchical rather than flat—that is, it can create nested results based on the tables in the query’s join clause.

Example-

SELECT        [Emp Id] as EmpId       ,[Employee Name] as EmployeeName       ,[Dept Id] as Deptid       ,[EmployeeRatePerDay]   FROM         EmployeeMaster   order by         EmpId   FOR XML AUTO

After execution of the above query, we will get the query results as a hyperlink and after clicking on that hyperlink, we will see the output rendered as properly formatted XML in a color-coded window that supports expanding and collapsing nodes.

<EmployeeMaster EmpId="E0001" EmployeeName="Ryan Arjun" Deptid="D001" EmployeeRatePerDay="240.00" /> <EmployeeMaster EmpId="E0002" EmployeeName="Tony Towery" Deptid="D001" EmployeeRatePerDay="230.00" /> <EmployeeMaster EmpId="E0003" EmployeeName="Lucy Gray" Deptid="D001" EmployeeRatePerDay="250.00" /> <EmployeeMaster EmpId="E0004" EmployeeName="Will Smith" Deptid="D002" EmployeeRatePerDay="245.00" /> <EmployeeMaster EmpId="E0005" EmployeeName="Chao Milk" Deptid="D002" EmployeeRatePerDay="225.00" /> <EmployeeMaster EmpId="E0006" EmployeeName="Chris Gyal" Deptid="D002" EmployeeRatePerDay="210.00" /> <EmployeeMaster EmpId="E0007" EmployeeName="Bill Gray" Deptid="D003" EmployeeRatePerDay="190.00" /> <EmployeeMaster EmpId="E0008" EmployeeName="Red Bill" Deptid="D003" EmployeeRatePerDay="210.00" /> <EmployeeMaster EmpId="E0009" EmployeeName="Tom Ramsay" Deptid="D003" EmployeeRatePerDay="200.00" />

FOR XML EXPLICIT
This is the most powerful and flexible of the three original FOR XML options. FOR XML EXPLICIT is more complex than its predecessors. To use FOR XML EXPLICIT, we must have two SELECT statements with a UNION.

I'm trying to cover it now for completeness, but recommend using the simpler FOR XML PATH feature.

SELECT       1 as Tag, -- Tag this result set as level 1       Null as parent -- Level 1 has no parent       ,[Dept Id] as [EMP!1!DeptId] -- level 1 value       ,NULL as [EMP2!2!EmployeeId] -- level 2 value       ,NULL as [EMP2!2!EployeeName]  -- level 2 value          ,Null as [EMP2!2!EmployeeRatePerDay]-- level 2 value   FROM EmployeeMaster EMP   where ([Dept Id]='D001' and [Emp Id]='E0001') UNION ALL SELECT           2 -- Tag this resultset as level 2          ,1 -- Link to parent at level 1       ,[Dept Id] as DeptId       ,[Emp Id] as EmployeeId       ,[Employee Name]  as EmployeeName          ,EmployeeRatePerDay   FROM EmployeeMaster   where ([Dept Id]='D001' )   order by [EMP!1!DeptId]   FOR XML EXPLICIT

After execution of the above query, we will get the query results as a hyperlink and after clicking on that hyperlink, we will see the output rendered as properly formatted XML in a color-coded window that supports expanding and collapsing nodes.

<EMP DeptId="D001">   <EMP2 EmployeeId="E0001" EployeeName="Ryan Arjun" EmployeeRatePerDay="240.00" />   <EMP2 EmployeeId="E0002" EployeeName="Tony Towery" EmployeeRatePerDay="230.00" />   <EMP2 EmployeeId="E0003" EployeeName="Lucy Gray" EmployeeRatePerDay="250.00" /> </EMP>

Additional FOR XML Features

The TYPE Option- Using the TYPE option, FOR XML can output to an xml data type from a SELECT statement, which in turn allows you to nest the results of SELECT…FOR XML into another SELECT statement.

SELECT [Emp Id]       ,(          SELECT [Employee Name]               ,[Dept Id]               ,[EmployeeRatePerDay]            FROM [EmployeeMaster]          where [Emp Id]=emp.[Emp Id]          FOR XML AUTO, TYPE          ) Details   FROM [dbo].[EmployeeMaster] emp

Using the TYPE option with FOR XML AUTO to cast a subquery result set as an xml data type as shown below-

Emp Id	Details
E0001	<EmployeeMaster Employee_x0020_Name="Ryan Arjun" Dept_x0020_Id="D001" EmployeeRatePerDay="240.00" />
E0002	<EmployeeMaster Employee_x0020_Name="Tony Towery" Dept_x0020_Id="D001" EmployeeRatePerDay="230.00" />
E0003	<EmployeeMaster Employee_x0020_Name="Lucy Gray" Dept_x0020_Id="D001" EmployeeRatePerDay="250.00" />
E0004	<EmployeeMaster Employee_x0020_Name="Will Smith" Dept_x0020_Id="D002" EmployeeRatePerDay="245.00" />
E0005	<EmployeeMaster Employee_x0020_Name="Chao Milk" Dept_x0020_Id="D002" EmployeeRatePerDay="225.00" />
E0006	<EmployeeMaster Employee_x0020_Name="Chris Gyal" Dept_x0020_Id="D002" EmployeeRatePerDay="210.00" />
E0007	<EmployeeMaster Employee_x0020_Name="Bill Gray" Dept_x0020_Id="D003" EmployeeRatePerDay="190.00" />
E0008	<EmployeeMaster Employee_x0020_Name="Red Bill" Dept_x0020_Id="D003" EmployeeRatePerDay="210.00" />
E0009	<EmployeeMaster Employee_x0020_Name="Tom Ramsay" Dept_x0020_Id="D003" EmployeeRatePerDay="200.00" />

FOR XML PATH - As we already knew, FOR XML PATH gives a fine control over the generated XML much like FOR XML EXPLICIT does, but is much simpler to use.

SELECT [Emp Id] as [@EmployeeID]       ,[Employee Name] as [Employee/Name]       ,[Dept Id]  as [Employee/Dept]       ,[EmployeeRatePerDay] as  [Employee/RatePerDay]   FROM [dbo].[EmployeeMaster]   FOR XML PATH('Employee')

After execution of the above query, we will get the query results as a hyperlink and after clicking on that hyperlink, we will see the output rendered as properly formatted XML in a color-coded window that supports expanding and collapsing nodes.

<Employee EmployeeID="E0001">   <Employee>     <Name>Ryan Arjun</Name>     <Dept>D001</Dept>     <RatePerDay>240.00</RatePerDay>   </Employee> </Employee> <Employee EmployeeID="E0002">   <Employee>     <Name>Tony Towery</Name>     <Dept>D001</Dept>     <RatePerDay>230.00</RatePerDay>   </Employee> </Employee>

Notice that the EMP ID column is rendered as an attribute. This is because it was aliased as @EmployeeID, and the @-symbol in XPath means “attribute.” Now, using FOR XML PATH to shape XML output for a two-level hierarchy as shown below

SELECT [Emp Id] as [@EmployeeID],        (           Select        [Employee Name] as [@EmployeeName]       ,[Dept Id]  as [@EmployeeDept]       ,[EmployeeRatePerDay] as  [@EmployeeRatePerDay]           from [dbo].[EmployeeMaster]           where [Emp Id]= emp.[Emp Id]           FOR XML PATH('Employee'), TYPE)   FROM [dbo].[EmployeeMaster] emp   FOR XML PATH('Employee')

After execution of the above query, we will get the query results as a hyperlink and after clicking on that hyperlink, we will see the output rendered as properly formatted XML in a color-coded window that supports expanding and collapsing nodes.

<Employee EmployeeID="E0001">   <Employee EmployeeName="Ryan Arjun" EmployeeDept="D001" EmployeeRatePerDay="240.00" /> </Employee> <Employee EmployeeID="E0002">   <Employee EmployeeName="Tony Towery" EmployeeDept="D001" EmployeeRatePerDay="230.00" /> </Employee> <Employee EmployeeID="E0003">   <Employee EmployeeName="Lucy Gray" EmployeeDept="D001" EmployeeRatePerDay="250.00" /> </Employee> <Employee EmployeeID="E0004">   <Employee EmployeeName="Will Smith" EmployeeDept="D002" EmployeeRatePerDay="245.00" /> </Employee> <Employee EmployeeID="E0005">   <Employee EmployeeName="Chao Milk" EmployeeDept="D002" EmployeeRatePerDay="225.00" /> </Employee> <Employee EmployeeID="E0006">   <Employee EmployeeName="Chris Gyal" EmployeeDept="D002" EmployeeRatePerDay="210.00" /> </Employee> <Employee EmployeeID="E0007">   <Employee EmployeeName="Bill Gray" EmployeeDept="D003" EmployeeRatePerDay="190.00" /> </Employee> <Employee EmployeeID="E0008">   <Employee EmployeeName="Red Bill" EmployeeDept="D003" EmployeeRatePerDay="210.00" /> </Employee> <Employee EmployeeID="E0009">   <Employee EmployeeName="Tom Ramsay" EmployeeDept="D003" EmployeeRatePerDay="200.00" /> </Employee>