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.
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.
@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
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.
It is possible with MS SQL Server 2014.
See: http://msdn.microsoft.com/en-us/library/dn133079.aspx
Here is an example of SQL generation code (from MSDN):
-- create a database with a memory-optimized filegroup and a container.
CREATE DATABASE imoltp
GO
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod
ALTER DATABASE imoltp SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
USE imoltp
GO
-- create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- create a non-durable table. Data will not be persisted, data loss if the server turns off unexpectedly
CREATE TABLE dbo.UserSession (
SessionId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000),
UserId int NOT NULL,
CreatedDate DATETIME2 NOT NULL,
ShoppingCartId INT,
INDEX ix_UserId NONCLUSTERED HASH (UserId) WITH (BUCKET_COUNT=400000)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO
A good blog post here but basically prefix local temp tables with # and global temp with ## - eg
CREATE TABLE #localtemp
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%!
The syntax you want is:
create table #tablename
The # prefix identifies the table as a temporary table.
