mysql - temporales - ¿Admite MS-SQL las tablas en memoria?

tablas temporales sql server 2016

Question

Recientemente, comencé a cambiar algunas de nuestras aplicaciones para admitir MS SQL Server como un back-end alternativo.

Uno de los problemas de compatibilidad que encontré es el uso de CREATE TEMPORARY TABLE de MySQL para crear tablas en memoria que contienen datos para un acceso muy rápido durante una sesión sin necesidad de almacenamiento permanente.

¿Cuál es el equivalente en MS SQL?

Un requisito es que necesito poder usar la tabla temporal como cualquier otra, especialmente JOIN con las permanentes.

Answer 1

@Keith

Esta es una idea errónea común: las variables de tabla NO necesariamente se almacenan en la memoria. De hecho, SQL Server decide si mantener la variable en la memoria o derramarla en TempDB. No hay una manera confiable (al menos en SQL Server 2005) de garantizar que los datos de la tabla se guarden en la memoria. Para obtener información más detallada, mira aquí

Answer 2

CREATE TABLE #tmptablename

Use el prefijo de signo hash / pound

Answer 3

La sintaxis que desea es:

crear tabla #tablename

El prefijo # identifica la tabla como una tabla temporal.

Answer 4

Puede declarar una "variable de tabla" en SQL Server 2005, así:

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

A continuación, se refiere a ella como una variable:

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

No es necesario soltarlo; desaparece cuando la variable queda fuera del alcance.

Answer 5

Puede crear variables de tabla (en memoria) y dos tipos diferentes de tabla temporal:

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

Editar:

Tras los comentarios, creo que esto necesita una pequeña aclaración.

#table y ##table siempre estarán en TempDB.

@Table variables @Table normalmente estarán en la memoria, pero no se garantiza que lo sean. SQL decide en función del plan de consulta y utiliza TempDB si es necesario.

Answer 6

Una buena publicación de blog aquí, pero básicamente prefija las tablas temporales locales con # y temperatura global con ## - por ejemplo

CREATE TABLE #localtemp

Answer 7

Entiendo lo que estás tratando de lograr. ¡Bienvenido al mundo de una variedad de bases de datos!

SQL Server 2000 admite tablas temporales creadas prefijando un # al nombre de la tabla, lo que la convierte en una tabla temporal accesible localmente (local para la sesión) y anterior ## al nombre de la tabla, para tablas temporales accesibles globalmente, por ejemplo #MyLocalTable y ## MyGlobalTable respectivamente.

SQL Server 2005 y posteriores admiten tablas temporales (locales, globales) y variables de tabla: ¡tenga cuidado con la nueva funcionalidad en variables de tabla en SQL 2008 y en la versión dos! La diferencia entre las tablas temporales y las variables de la tabla no es tan grande, pero radica en la forma en que el servidor de la base de datos las maneja.

No me gustaría hablar sobre versiones anteriores de SQL Server como 7, 6, aunque he trabajado con ellos y de todos modos es de donde vengo :-)

Es común pensar que las variables de tabla siempre residen en la memoria, pero esto es incorrecto. Según el uso de la memoria y el volumen de transacciones del servidor de la base de datos, las páginas de una variable de tabla se pueden exportar desde la memoria y se pueden escribir en tempdb y el resto del procesamiento se realiza allí (en tempdb).

Tenga en cuenta que tempdb es una base de datos en una instancia sin objetos permanentes en la naturaleza, pero es responsable de manejar las cargas de trabajo que implican transacciones secundarias como la clasificación y otros trabajos de procesamiento que son de naturaleza temporal. Por otro lado, las variables de tabla (generalmente con datos más pequeños) se guardan en memoria (RAM) haciéndolas más rápidas de acceder y por lo tanto menos disco IO en términos de usar el disco tempdb cuando se usan variables de tabla con datos más pequeños en comparación con tablas temporales que siempre inicia sesión en tempdb.

Las variables de tabla no se pueden indexar mientras que las tablas temporales (tanto locales como globales) se pueden indexar para un procesamiento más rápido en caso de que la cantidad de datos sea grande. Por lo tanto, conoce su elección en caso de un procesamiento más rápido con volúmenes de datos más grandes mediante transacciones temporales. También vale la pena señalar que las transacciones en variables de tabla por sí solas no se registran y no se pueden deshacer mientras que las que se realizan en tablas temporales se pueden revertir.

En resumen, las variables de tabla son mejores para datos más pequeños, mientras que las tablas temporales son mejores para datos más grandes que se procesan temporalmente. Si también desea un control de transacción adecuado mediante el uso de bloques de transacción, las variables de tabla no son una opción para revertir transacciones, por lo que en este caso es mejor utilizar tablas temporales.

Por último, las tablas temporales siempre aumentarán el IO del disco, ya que siempre usan tempdb, mientras que las variables de la tabla pueden no aumentarlo, dependiendo de los niveles de estrés de la memoria.

¡Avíseme si desea consejos sobre cómo ajustar su tempdb para obtener un rendimiento mucho más rápido y superar el 100%!

Answer 8

Es posible con MS SQL Server 2014.

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

Aquí hay un ejemplo de código de generación SQL (de 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