Search code examples
mysqlsql-serverrdbmsportability

Does MS-SQL support in-memory tables?

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.

Solution

  • @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