SQL: Quick Overview: Temporary Tables in SQL Server 2005


Quick Overview: Temporary Tables in SQL Server 2005

By Abhijit Jana | 22 Sep 2009

Table of Contents

Creating Temporary Table in SQL Server 2005 Storage Location of Temporary Table When to Use Temporary Tables? Points to Remember Before Using Temporary Tables Alternative Approach: Table Variable


SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

In this article, I am just going to give a quick overview for beginners on those temporary tables. Please give your valuable suggestions and feedback to improve this article.

Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

  • Local Temp Table
  • Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Creating Temporary Table in SQL Server 2005

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.

Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server 2005:

Collapse | Copy Code

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

Collapse | Copy Code

insert into #LocalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

Collapse | Copy Code

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:

Collapse | Copy Code

Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.

Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:

Collapse | Copy Code

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

Collapse | Copy Code

insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

Collapse | Copy Code

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Storage Location of Temporary Table

Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.

Now, if we deeply look into the name of Local Temporary table names, a ‘dash‘ is associated with each and every table name along with an ID. Have a look at the image below:

SQL server does all this automatically, we do not need to worry about this; we need to only use the table name.

When to Use Temporary Tables?

Below are the scenarios where we can use temporary tables:

  • When we are doing large number of row manipulation in stored procedures.
  • This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
  • When we are having a complex join operation.

Points to Remember Before Using Temporary Tables

  • Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
  • Number of rows and columns need to be as minimum as needed.
  • Tables need to be deleted when they are done with their work.

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

Collapse | Copy Code

Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The below scripts are used to insert and read the records for Tablevariables:

Collapse | Copy Code

insert into @TempTableVariable values ( 1, 'Abhijit','India');

Now select records from that tablevariable:

Collapse | Copy Code

select * from @TempTableVariable

When to Use Table Variable Over Temp Table

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.


SQL: Derived Tables in SQL


Derived Tables in SQL

The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I’ve yet done with SQL Server is using derived tables.

If you’ve used a VIEW before, you’ve used a more formal, more correct form of a derived table. For example, we could do the following:

 CREATE VIEW vwEmployeesFromNewYork AS
 SELECT * FROM Employee
 WHERE State = "NY"

Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:

 SELECT LastName, FirstName
 FROM vwEmployeesFromNewYork
 WHERE LastName = "Smith"
 ORDER BY FirstName

However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what’s important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:

 SELECT LastName, FirstName
 (SELECT * FROM Employee
 WHERE State = "NY") AS EmployeeDerivedTable
 WHERE LastName = "Smith"
 ORDER BY FirstName

Isn’t that neat? What we are doing is first getting the result set from our derived table (the SELECT statement in the FROM clause). Once we have that resultset, it is as though it was a table in itself. We then perform the SELECT on the derived table, returning our results! You can find another example of using derived tables here on 4GuysFromRolla.com in the article Obtaining Ranked Values from a Table page.

SQL SERVER – Simple Example of Reading XML File Using T-SQL


SQL SERVER – Simple Example of Reading XML File Using T-SQL

February 13, 2009 by pinaldave

In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.

Following is the XML which we will read using T-SQL:

Following is the T-SQL script which we will be used to read the XML:

SET @MyXML = '<SampleXML>
< Colors>
< Color1>White</Color1>
< Color2>Blue</Color2>
< Color3>Black</Color3>
< Color4 Special="Light">Green</Color4>
< Color5>Red</Color5>
< /Colors>
< Fruits>
< Fruits1>Apple</Fruits1>
< Fruits2>Pineapple</Fruits2>
< Fruits3>Grapes</Fruits3>
< Fruits4>Melon</Fruits4>
< /Fruits>
< /SampleXML>'

a.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,
a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,
a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,
a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ’+
+a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,
a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,
a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,
a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,
a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,
a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b)

Please note in above T-SQL statement XML attributes is read the same as XML Value.

SQL SERVER – Simple Example of Creating XML File Using T-SQL


SQL SERVER – Simple Example of Creating XML File Using T-SQL

February 12, 2009 by pinaldave

I always want to learn SQL Server and XML. Let us go over very simple example today about how to create XML using SQL Server.

Please also read related article here SQL SERVER – Simple Example of Reading XML File Using T-SQL.

Following is the XML which we want to create:

T-SQL Script to generate above XML:

SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
XML PATH('Colors'),
( SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
XML PATH('Fruits'),

Every XML has two elements.

1) Attributes and 2) Value.

In my above example color4 has attribute along with value. Make sure to specify attribute before the value is defined otherwise it will give error. We will talk about this in other article.

SQL: Using Common Table Expressions


Using Common Table Expressions

SQL Server 2008 R2

·SQL Server 2008

·SQL Server 2005

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

· Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

· Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

· Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

· Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

Performance/SQL Server : Partitioning a SQL Server Database Table


Partitioning a SQL Server Database Table

By Mike Chapple, About.com Guide

Looking to optimize the performance of your SQL Server database? If your database contains very large tables, you may benefit from partitioning those tables onto separate filegroups. This technology, introduced in SQL Server 2005, allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance.

Partitioning a SQL Server database table is a three-step process:

1. Create the partition function

2. Create the partition scheme

3. Partition the table

The remainder of this article explores each of those steps in further detail.

Step 1: Creating a Partition Function

The partition function defines [u]how[/u] you want SQL Server to partition the data. At this point, we’re not concerned with any particular table, we’re just generically defining a technique for splitting data.

We define the partitions by specifying the boundaries of each partition. For example, suppose we have a Customers table that contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We might decide to partition that table into four equally spaced partitions, using the following partition function (I’ll call it customer_partfunc):

CREATE PARTITION FUNCTION customer_partfunc (int)


FOR VALUES (250000, 500000, 750000)

These boundaries define four partitions. The first contains all values less than 250,000. The second contains values between 250,000 and 499,999. The third contains values between 500,000 and 749,999. All values greater than or equal to 750,000 go in the fourth partition.

Notice that I used the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than [u]or equal to[/u] 250,000; the second partition would have included values between 250,001 and 500,000, and so on.

Step 2: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

CREATE PARTITION SCHEME customer_partscheme

AS PARTITION customer_partfunc

TO (fg1, fg2, fg3, fg4)

Notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. That’s where the power of reusability comes into play. We could use this partition scheme (or just the partition function, if we desired) on any number of database tables.

Step 3: Partitioning a Table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

For example, if you wanted to create a customer table using our partition scheme, you would use the following Transact-SQL statement:

CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)

ON customer_partscheme (CustomerNumber)

That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!

One more article on SQL Server 2005 Partitioning


Sql Server 2005 Partitioning

A very interesting an powerful feature of Sql Server 2005 is called Partitioning.In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed.

This allows you to operate on a partition even with performace critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available, all the data in that partition are available for quering, even if the restore is not yet fully completed.

Here a simple script to begin to make some test on your own:

use adventureworks