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.