Modificar tablas con optimización para memoria

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Los cambios de esquema e índice en las tablas optimizadas para memoria se pueden realizar mediante la ALTER TABLE instrucción . En SQL Server 2016 y Azure SQL Database, las operaciones en tablas con optimización para memoria ALTER TABLE se realizan sin conexión, lo que significa que la tabla no está disponible para consultas mientras la operación está en curso. La aplicación de base de datos puede seguir ejecutándose, y cualquier operación que tenga acceso a la tabla se bloqueará hasta que se complete el proceso de modificación. Es posible combinar varias operaciones ADD, DROP o ALTER en una sola ALTER TABLE instrucción.

ALTER TABLE

La ALTER TABLE sintaxis se usa para realizar cambios en el esquema de tabla, así como para agregar, eliminar y volver a generar índices. Los índices se consideran parte de la definición de tabla:

• Sintaxis ALTER TABLE ... ADD/DROP/ALTER INDEX solo se admite para tablas optimizadas para memoria.

• Sin usar una instrucción ALTER TABLE, las instrucciones CREATE INDEX, DROP INDEX, ALTER INDEX y PAD_INDEX no se admiten para índices en tablas optimizadas para memoria.

Se admiten los siguientes tipos de modificaciones:

• Cambiar el número de buckets

• Agregar y quitar un índice

• Cambiar, agregar y quitar una columna

• Agregar y quitar una restricción

Para obtener más información sobre la ALTER TABLE funcionalidad y la sintaxis completa, vea ALTER TABLE (Transact-SQL)

Dependencia vinculada al esquema

Es necesario que los procedimientos almacenados compilados de forma nativa estén enlazados a un esquema, es decir, que tengan una dependencia enlazada a esquema en las tablas con optimización para memoria a las que acceden y las columnas a las que hacen referencia. Una dependencia enlazada a esquema es una relación entre dos entidades que evita que la entidad a la que se hace referencia se elimine o se modifique de manera incompatible mientras exista la entidad de referencia.

Por ejemplo, si un procedimiento almacenado compilado de forma nativa enlazado a esquema hace referencia a una columna c1 de la tabla mytable, la columna c1 no se puede quitar. Del mismo modo, si hay un procedimiento de este tipo con una INSERT instrucción sin lista de columnas (por ejemplo, INSERT INTO dbo.mytable VALUES (...)), no se puede quitar ninguna columna de la tabla.

Registro de ALTER TABLE en tablas optimizadas para memoria

En una tabla optimizada para memoria, la mayoría ALTER TABLE de los escenarios ahora se ejecutan en paralelo y dan como resultado una optimización de escrituras en el registro de transacciones. La optimización se consigue únicamente al registrar los cambios de metadatos en el registro de transacciones. Sin embargo, las siguientes operaciones ALTER TABLE se ejecutan en un solo hilo y no están optimizadas para el registro en el log.

En este caso, la operación de un solo subproceso registraría todo el contenido de la tabla modificada en el registro de transacciones. A continuación se muestra una lista de operaciones de un solo hilo:

• Modificar o añadir una columna para usar un tipo de objeto grande (LOB): nvarchar(max), varchar(max) o varbinary(max).

• Agregar o quitar un índice COLUMNSTORE.

• Casi todo lo que afecta a una columna fuera de fila.

  • Hacer que una columna de la fila salga de la fila.
  • Hacer que una columna fuera de la fila pase a estar en la fila.
  • Crear una nueva columna fuera de fila.
  • Excepción: La ampliación de una columna que ya está fuera de fila se registra de forma optimizada.

Ejemplos

En el ejemplo siguiente se modifica el número de cubos de un índice de hash existente. Esto vuelve a generar el índice de hash con el nuevo número de cubos, mientras que las demás propiedades del índice de hash permanecen iguales.

ALTER TABLE Sales.SalesOrderDetail_inmem
       ALTER INDEX imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID  
              REBUILD WITH (BUCKET_COUNT=67108864);  
GO

En el ejemplo siguiente se agrega una columna con una restricción NOT NULL y con una DEFAULT definición, y se usa WITH VALUES para proporcionar valores para cada fila existente de la tabla. Si no se utiliza WITH VALUES, cada fila tiene el valor NULL en la nueva columna.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       ADD Comment NVARCHAR(100) NOT NULL DEFAULT N'' WITH VALUES;  
GO

En el ejemplo siguiente se agrega una restricción de clave principal a una columna existente.

CREATE TABLE dbo.UserSession (
   SessionId int not null,
   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  
  
ALTER TABLE dbo.UserSession  
       ADD CONSTRAINT PK_UserSession PRIMARY KEY NONCLUSTERED (SessionId);  
GO

En el siguiente ejemplo se quita un índice.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       DROP INDEX ix_ModifiedDate;  
GO

En el siguiente ejemplo se agrega un índice.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       ADD INDEX ix_ModifiedDate (ModifiedDate);  
GO  

En el siguiente ejemplo se agregan varias columnas, con un índice y restricciones.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       ADD    CustomerID int NOT NULL DEFAULT -1 WITH VALUES,  
              ShipMethodID int NOT NULL DEFAULT -1 WITH VALUES,  
              INDEX ix_Customer (CustomerID);  
GO  

Consulte también

Tablas optimizadas para la memoria