MySQL MS-SQL 是否支持内存表？

Does MS-SQL support in-memory tables?

提问by Hanno Fietz

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

最近，我开始更改我们的一些应用程序以支持MS SQL Server作为替代后端。

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.

我遇到的兼容性问题之一是使用 MySQL 的 CREATE TEMPORARY TABLE 创建内存表，这些表保存数据以便在会话期间非常快速地访问而无需永久存储。

What is the equivalent in MS SQL?

MS SQL 中的等价物是什么？

A requirement is that I need to be able to use the temporary table just like any other, especially JOINit with the permanent ones.

一个要求是我需要能够像其他任何人一样使用临时表，尤其是JOIN它与永久表。

采纳答案by Manu

@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

这是一个常见的误解：表变量不一定存储在内存中。事实上，SQL Server 决定是将变量保留在内存中还是将其溢出到 TempDB。没有可靠的方法（至少在 SQL Server 2005 中）确保表数据保存在内存中。有关更多详细信息，请查看此处

回答by Keith

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.

根据反馈，我认为这需要一些澄清。

#tableand ##tablewill always be in TempDB.

#table并且##table将始终在 TempDB 中。

@Tablevariables 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.

@Table变量通常会在内存中，但不能保证在内存中。SQL 根据查询计划做出决定，并在需要时使用 TempDB。

回答by Joezer

It is possible with MS SQL Server 2014.

MS SQL Server 2014 是可能的。

See: http://msdn.microsoft.com/en-us/library/dn133079.aspx

请参阅：http: //msdn.microsoft.com/en-us/library/dn133079.aspx

Here is an example of SQL generation code (from MSDN):

这是 SQL 生成代码的示例（来自 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

回答by Matt Hamilton

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

您可以在 SQL Server 2005 中声明一个“表变量”，如下所示：

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.

无需删除它 - 当变量超出范围时它就会消失。

回答by JamesSugrue

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

这里有一篇很好的博客文章，但基本上用 # 前缀本地临时表，用 ## 前缀全局临时 - 例如

CREATE TABLE #localtemp

回答by JamesSugrue

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 2000 支持通过在表名前加上 # 前缀来创建临时表，使其成为本地可访问的临时表（会话本地）并在表名前加上 ##，用于全局可访问的临时表，例如 #MyLocalTable 和 ##MyGlobalTable分别。

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.

SQL Server 2005 及更高版本同时支持临时表（本地、全局）和表变量 - 注意 SQL 2008 和第二版中表变量的新功能！临时表和表变量之间的区别不是很大，而是在于数据库服务器处理它们的方式。

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 :-)

我不想谈论旧版本的 SQL 服务器，例如 7、6，尽管我曾与它们一起工作过，而且无论如何我都是从这里来的 :-)

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).

通常认为表变量总是驻留在内存中，但这是错误的。根据内存使用情况和数据库服务器的事务量，表变量的页面可能会从内存中导出并写入 tempdb，其余的处理发生在那里（在 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.

请注意，tempdb 是一个实例上的数据库，本质上没有永久对象，但它负责处理涉及诸如排序之类的副事务的工作负载，以及其他本质上是临时的处理工作。另一方面，表变量（通常具有较小的数据）保存在内存 (RAM) 中，这使得它们访问速度更快，因此在使用具有较小数据的表变量时使用 tempdb 驱动器的磁盘 IO 较少，而临时表总是登录临时数据库。

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.

最后，临时表总是会增加磁盘 IO，因为它们总是使用 tempdb 而表变量可能不会增加它，这取决于内存压力水平。

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

如果您需要有关如何调整 tempdb 以获得更快的性能以超过 100% 的提示，请告诉我！

回答by Tundey

The syntax you want is:

你想要的语法是：

create table #tablename

创建表#tablename

The # prefix identifies the table as a temporary table.

# 前缀将该表标识为临时表。

回答by Iain Holder

CREATE TABLE #tmptablename

创建表#tmptablename

Use the hash/pound sign prefix

使用哈希/磅符号前缀