Поддерживает ли MS-SQL таблицы в памяти?

@Кит

Это распространенное заблуждение: табличные переменные НЕ обязательно хранятся в памяти. На самом деле SQL Server решает, оставить ли переменную в памяти или передать ее в TempDB. Не существует надежного способа (по крайней мере, в SQL Server 2005) гарантировать, что данные таблицы хранятся в памяти. Для получения более подробной информации см. здесь

Вы можете создавать табличные переменные (в памяти) и два разных типа временных таблиц:

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

Изменить:

После обратной связи я думаю, что это нуждается в небольшом разъяснении.

#table и ##table всегда будут в TempDB.

@Table переменные обычно находятся в памяти, но это не гарантируется. SQL принимает решение на основе плана запроса и при необходимости использует TempDB.

Вы можете объявить «табличную переменную» в SQL Server 2005 следующим образом:

declare @foo table (
    Id int,
    Name varchar(100)
);

Затем вы ссылаетесь на него так же, как на переменную:

select * from @foo f
    join bar b on b.Id = f.Id

Его не нужно удалять — он исчезает, когда переменная выходит за пределы области видимости.

Это возможно с MS SQL Server 2014.

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

Вот пример кода генерации 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

Хороший пост в блоге здесь, но в основном локальные временные таблицы добавляются к префиксу # и глобальная температура с ## - например

CREATE TABLE #localtemp

Я понимаю, чего вы пытаетесь достичь. Добро пожаловать в мир разнообразных баз данных!

SQL Server 2000 поддерживает временные таблицы, созданные путем добавления префикса # к имени таблицы, что делает ее локально доступной временной таблицей (локальной для сеанса) и предшествующего ## имени таблицы для глобально доступных временных таблиц, например, #MyLocalTable и ##MyGlobalTable соответственно.

SQL Server 2005 и более поздние версии поддерживают как временные таблицы (локальные, глобальные), так и табличные переменные — обратите внимание на новые функции для табличных переменных в SQL 2008 и выпустите две! Разница между временными таблицами и табличными переменными не так велика, но заключается в том, как сервер базы данных их обрабатывает.

Я бы не хотел говорить о более старых версиях SQL-сервера, таких как 7, 6, хотя я работал с ними, и в любом случае я пришел оттуда :-)

Принято считать, что табличные переменные всегда находятся в памяти, но это неверно. В зависимости от использования памяти и объема транзакций сервера базы данных страницы табличных переменных могут быть экспортированы из памяти и записаны в базу данных tempdb, а остальная обработка выполняется там (в базе данных tempdb).

Обратите внимание, что tempdb — это база данных на экземпляре, не имеющая постоянных объектов по своей природе, но она отвечает за обработку рабочих нагрузок, включающих побочные транзакции, такие как сортировка, и другую работу по обработке, которая носит временный характер. С другой стороны, табличные переменные (обычно с меньшими данными) хранятся в памяти (ОЗУ), что ускоряет доступ к ним и, следовательно, уменьшает дисковый ввод-вывод с точки зрения использования диска tempdb при использовании табличных переменных с меньшими данными по сравнению с временными таблицами, которые всегда войдите в базу данных tempdb.

Табличные переменные не могут быть проиндексированы, в то время как временные таблицы (как локальные, так и глобальные) могут быть проиндексированы для более быстрой обработки в случае большого объема данных. Таким образом, вы знаете свой выбор в случае более быстрой обработки больших объемов данных временными транзакциями. Также стоит отметить, что транзакции только с табличными переменными не регистрируются и не могут быть отброшены, в то время как транзакции, выполненные с временными таблицами, могут быть отброшены!

Таким образом, табличные переменные лучше подходят для небольших данных, а временные таблицы лучше подходят для временной обработки больших данных. Если вам также нужен надлежащий контроль транзакций с помощью блоков транзакций, табличные переменные не подходят для отката транзакций, поэтому в этом случае вам лучше использовать временные таблицы.

Наконец, временные таблицы всегда будут увеличивать дисковый ввод-вывод, поскольку они всегда используют базу данных tempdb, в то время как табличные переменные могут не увеличивать ее, в зависимости от уровня нагрузки на память.

Дайте мне знать, если вам нужны советы о том, как настроить базу данных tempdb, чтобы добиться более высокой производительности, превышающей 100%!

Синтаксис, который вы хотите:

создать таблицу #имя_таблицы

Префикс # идентифицирует таблицу как временную.

СОЗДАТЬ ТАБЛИЦУ #tmptablename

Используйте префикс решётки/знака фунта стерлингов