Does MS-SQL support in-memory tables?

Question

Recently, I started changing some of our applications to support MS SQL Server as an alternative back end.

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

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 2

@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