Does MS-SQL support in-memory tables?

Question

I've grown up as a programmer using MySQL all the way. Recently, I started changing some of our applications to support MS SQL Server as an alternative backend. One of the compatibility issues I ran into is the use of MySQL's CREATE TEMPORARY TABLE to create in-memory tables that hold data for very fast access during a session with no need for permanent storage.

What is the equivalent in MS SQL? A requirement is that I need to be able to use the temporary table just like any other, especially JOIN it with the permanent ones.

Answer 1

CREATE TABLE #tmptablename

Use the hash/pound sign prefix

Answer 2

You can declare a "table variable" in SQL Server 2005, like this:

declare @foo table (
    Id int,
    Name varchar(100)
);

You then refer to it just like a variable:

select * from @foo f
    join bar b on b.Id = f.Id

No need to drop it - it goes away when the variable goes out of scope.

Answer 3

The syntax you want is:

create table #tablename

The # prefix identifies the table as a temporary table.

Answer 4

A good blog post here but basically prefix local temp tables with # and global temp with ## - eg

CREATE TABLE #localtemp

Answer 5

You can create table variables (in memory), and two different types of temp table:

--visible only to me, in memory (SQL 2000 and above only)
declare @test table (
    Field1 int,
    Field2 nvarchar(50)
);

--visible only to me, stored in tempDB
create table #test (
    Field1 int,
    Field2 nvarchar(50)
)

--visible to everyone, stored in tempDB
create table ##test (
    Field1 int,
    Field2 nvarchar(50)
)

---

Edit:

Following feedback I think this needs a little clarification.

#table and ##table will always be in TempDB.

@Table variables will normally be in memory, but are not guaranteed to be. SQL decides based on the query plan, and uses TempDB if it needs to.

Answer 6

@Keith

This is a common misconception: Table variables are NOT necessarily stored in memory. In fact SQL Server decides whether to keep the variable in memory or to spill it to TempDB. There is no reliable way (at least in SQL Server 2005) to ensure that table data is kept in memory. For more detailed info look here

Answer 7

Hi Hanno,

I understand what you're trying to achieve. Welcome to the world of a variety of databases!

SQL server 2000 supports temporary tables created by prefixing a # to the table name, making it a locally accessible temporary table (local to the session) and preceding ## to the table name, for globally accessible temporary tables e.g #MyLocalTable and ##MyGlobalTable respectively.

SQL server 2005 and above support both temporary tables (local, global) and table variables - watch out for new functionality on table variables in SQL 2008 and release two! The difference between temporary tables and table variables is not so big but lies in the the way the database server handles them.

I would not wish to talk about older versions of SQL server like 7, 6, though I have worked with them and it's where I came from anyway :-)

It’s common to think that table variables always reside in memory but this is wrong. Depending on memory usage and the database server volume of transactions, a table variable's pages may be exported from memory and get written in tempdb and the rest of the processing takes place there (in tempdb).

Please note that tempdb is a database on an instance with no permanent objects in nature but it’s responsible for handling workloads involving side transactions like sorting, and other processing work which is temporary in nature. On the other hand, table variables (usually with smaller data) are kept in memory (RAM) making them faster to access and therefore less disk IO in terms of using the tempdb drive when using table variables with smaller data compared to temporary tables which always log in tempdb.

Table variables cannot be indexed while temporary tables (both local and global) can be indexed for faster processing in case the amount of data is large. So you know your choice in case of faster processing with larger data volumes by temporary transactions. It's also worth noting that transactions on table variables alone are not logged and can't be rolled back while those done on temporary tables can be rolled back!

In summary, table variables are better for smaller data while temporary tables are better for larger data being processed temporarily. If you also want proper transaction control using transaction blocks, table variables are not an option for rolling back transactions so you're better off with temporary tables in this case.

Lastly, temporary tables will always increase disk IO since they always use tempdb while table variables may not increase it, depending on the memory stress levels.

Let me know if you want tips on how to tune your tempdb to earn much faster performance to go above 100%! God bless!

Regards,

Chris Musasizi

Lead SQL DBA at MTN, Author & Expert at SQL Server Central & Experts Exchange respectively.

About Chris:

Chris is the Lead DBA at MTN, Uganda's leading mobile telecom which is part of MTN group, the sponsors of the World cup 2009. Chris works on Business Systems that demand close to 100% uptime in a year. Over the years, Chris has worked in the communications industry majorly with good background in bridging between database technology and business strategies to provide highly available, stable and optimised business solutions.

Chris has majorly worked with Oracle, MSSQL, Informix and MySQL database systems and storage engines, NetApp NAS and SAN storage systems. Chris primarily being certified Software engineer, started career as a developer (both web and distributed systems), then did Business Intelligence (BI), later did enterprise work, DBA, and finally managerial work. Prior to MTN, he was the Senior IT Manager at Posta Uganda, the leading postal and Courier Company in the country.

Feel free to mail: [email protected]

Have you read John 3:3, John 3:16? Knowledge is power! Together, let's work towards Heaven!

Answer 8

Hi,

I disagree with what is said.

Yes, SQL Server manage @table, #table and ##table (and table of course).

But this 3 (or 4) table types are VEREY DIFFERENT of a MEMORY TABLE.

MySQL don't drop a MEMORY TABLE. SQL Server drop @table, #table and ##table (@table in proc, #table in request and ##table in session of creator).

SQL Server NOT SUPPORT memory tables. None of previous table types is equivalent to MySQL memory table (a pity really).

Does not need?, SQL Server run very, very, good (for me). But a especific declaration for it would be good.

Regards.

(I've been programming SQL Server for 10 years ago).

Answer 9

It's unfortunate that you did not state what you disagree with.

To comment on the previous post, definitely each database platform has different ways it handles tables, memory and different objects.

To understand the SQL server platform, kindly read the article below:

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

Regards,

Chris Musasizi