Author Archives: Matt

Using temporary tables in SQL Server

In this article I’d like to discuss temporary tables and their usage in SQL Server. I’m going to discuss the reasons for their use and a few ways that they can be implemented in your database development efforts. Hopefully you’ll find them to be an invaluable weapon in your SQL armory.

What are temporary tables?

The clue’s in the name, really.  Temporary tables are often used to store data whilst you are carrying out some kind of transform or other processing on existing table data. Very often I use temporary tables to store information that might eventually find its way into another table whilst I’m updating it.  It’s also handy to use them whilst just playing around with your data, almost like a sandbox of sorts.

If they are just temporary, how (and where) are they stored?

All temporary databases are stored in the ‘tempdb’ database, which can be found in Databases > System Databases > tempdb in the Object Explorer in SSMS.

object explorer

Depending on the kind of temporary table created, they will exist in tempdb in a different location, and for a different length of time.

How do they work?  How long do these tables stick around if they are temporary?

Good question.  There are actually three ways of creating and using temporary tables in the tempdb database, and the way you create them has a different effect on how long they can be used for (and who/what they can be used by).

Let’s have a look at the three methods of creating temporary tables and talk about them in more detail.

1. Local temporary tables

A local temporary table is created in TSQL like this.  I’ll also populate with a row of data, just for you.

CREATE TABLE #MyTable(ID int, Value varchar(10))

Notice the single hashtag (nothing to do with Twitter, this).  This tells the database that we want to create a temporary table that is local to this particular session, in this particular case the T-SQL editor window that we are working in.

A temporary table created with a single hashtag isn’t accessible by anybody else or any other process.  The second the process that created it is closed, the table disappears completely.  Forever.

To prove this point, open a New Query and copy the SQL statement above into it.  Press F5 to  create and populate the temporary table, and then run the following simple SELECT in the same window to see if it worked ok :


If you’ve been following along, you should get the single row of data that we added earlier.

Now, open a New Query window and try the same SELECT statement:-

invalid object

This shouldn’t come as a surprise based on what I said earlier – the only process that can access the #MyTable object is the one that created it (the first query window you opened earlier).

If you close the query window containing the CREATE TABLE statement you will now no longer be able to access the temporary table you created.  It is gone.  Forever.

2.  Global Temporary Tables

Global temporary tables are pretty much the same to set as local, except you use two hashtags instead of two!  The main difference between global temporary tables is that they are available for use by anybody, not just the process that created them.

Let me show you what I mean.

Close all query editor windows , and then open a new one.  Copy the following:-

CREATE TABLE ##MyTable(ID int, Value varchar(10))

And press F5 to run it.  If you write a simple SELECT:-


You’ll notice  (hopefully unsurprisingly) that the table is (obviously) accessible.

Now (here’s the magic bit) open a second New Query and try the same select statement.

Global Temp Table

Perfect!  This temporary table is available to anything else that wants to use it, as long as the process that created it is still active. To demonstrate what I mean by this, close the query editor window where we created the temporary table and try to run the SELECT statement again.

Global Table Fail

And it’s gone.  Forever.  Again.

3.  Create a table in tempdb

You can create a table in tempdb in the same way as you would any other database:-

USE tempdb
CREATE TABLE  MyTable(ID int, Value varchar(10))

If you refresh the ‘Tables’ folder in Object Explorer, you’ll see our newly created table:-

object explorer temp

This table behaves like any other table in the database at this stage.  Any database user or process can access it, and it continues to act as a persistent database object (like any other).

Any table created in tempdb exists until the server is restarted or the table itself is dropped. Restarting the server will clear out any temporary tables created in the tempdb database and essentially recreate tempdb from scratch. This makes tempdb an ideal sandbox for playing around (just don’t rely on objects created in it being around forever!!).


The use of temporary tables is, I think, one of the more useful techniques in TSQL.  I use them quite often for ETL style processes, for example Create a temporary table > Load data into it > Manipulate/transform data > Do something with the output (perhaps INSERT INTO another table), but they can be put to use in so many different ways, and are really straightforward to put into practice.

Have fun!

Javascript: Extract a specific character in a string

Came across this today and really liked it as a quick tip.

If you want to extract the ‘n-th’ character from a string in Javascript, you can do this:-


So, if you wanted to pull out the 4th character, you would do this:-


The reason for subtracting one is that Javascript arrays are zero-based – that is, they start at zero rather than one.

You could also do the same with a variable:-

var string = "MATTHEW";
alert(string[3]); //returns the second 'T'


This is the first in a number of articles I intend to write whilst studying for my MCSE, so that I can not only consolidate the things I’ve learned, but also to share stuff with you guys.

The OFFSET-FETCH clause was new to SQL Server 2012 and provides a different means of filtering data. OFFSET-FETCH can provide some similar functionality to the TOP clause, but it has some particularly useful applications in your T-SQL queries.

The syntax looks something like this:-


It might look something like this in practice:-


So, what does it do?  Well it actually has two parts.

  • The first part is OFFSET x ROW/ROWS.  This part ‘skips’ the first x rows that the query would normally return, and then returns the rest.  This part can be used by itself, without using FETCH.
  • The second part is FETCH FIRST/NEXT x ROW/ROWS.  This part returns the next x rows after the OFFSET portion of the clause.  This can only be used if OFFSET is in use.

So, for our example above (OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY) the return would be the first 10 rows.

Before I go into a couple of other examples, there are a few things worth pointing out:-

  1. The OFFSET-FETCH clause requires ORDER BY to work, and must follow ORDER BY (in fact,some people see it as a part of ORDER BY).  Without ORDER BY, it won’t work.
  2. You can use either the word ‘ROW’ or ‘ROWS’ in either part of the clause with no difference in output.  They are completely interchangeable and it’s literally down to you how you want to use them – I like to switch them around to aid readability of the query.
  3. Similarly, you can use FIRST or NEXT in the second part of the clause and it doesn’t make any difference to the query output – again use whichever aids readability.
  4. Unlike TOP(x) there is no functionality for you to return a percentage of rows.
  5. You can’t use TOP(x) and OFFSET-FETCH at the same time!  Use one or the other.

To top it off lets look at a couple of examples using the AdventureWorks2012 database.  For each I’ll provide an example and an explanation of how the OFFSET-FETCH clause will affect the query output,

Example 1

SELECT [AddressLine1]
FROM [AdventureWorks2012].[Person].[Address]
ORDER BY AddressLine1

This example skips the first 10 rows, and then returns the next 20.  For the following examples the query should be the same, the only part that will change is the last line containing the OFFSET-FETCH clause.

Example 2 of query...

This example would exclude the first 30 rows of the query.  If without this your query would normally return 90 rows, you would get the last 60 by using this.

Example 3 of query...

This would return only the first row of the query

Example 4 of query...

Whilst some of the text might look different, this would give you exactly the same result as example 3.  Remember, the word ROW can be replaced with ROWS and the keyword NEXT and FIRST are also interchangeable.

One of the most useful applications of this clause is to provide paging for your data.  For example, you might want page one of a report to show records from 1 to 50:-


and then in page 2, the next 50 rows:-


And so on…quite useful, isn’t it?


2toria favourites – week ending 23/02/2014

Here are some of our favourtie web things from over the past week!  Enjoy!