Altering Memory-Optimized Tables

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Schema and index changes on memory-optimized tables can be performed by using the ALTER TABLE statement. In SQL Server 2016 and Azure SQL Database ALTER TABLE operations on memory-optimized tables are OFFLINE, meaning that the table is not available for querying while the operation is in progress. The database application can continue to run, and any operation that is accessing the table is blocked until the alteration process is completed. It is possible to combine multiple ADD, DROP or ALTER operations in a single ALTER TABLE statement.

Important

Azure SQL Managed Instance does not support memory-optimized tables in the General Purpose service tier.

ALTER TABLE

The ALTER TABLE syntax is used for making changes to the table schema, as well as for adding, deleting, and rebuilding indexes. Indexes are considered part of the table definition:

• The syntax ALTER TABLE ... ADD/DROP/ALTER INDEX is supported only for memory-optimized tables.

• Without using an ALTER TABLE statement, the statements CREATE INDEX, DROP INDEX, ALTER INDEX, and PAD_INDEX are not supported for indexes on memory-optimized tables.

The following types of alterations are supported:

• Changing the bucket count

• Adding and removing an index

• Changing, adding and removing a column

• Adding and removing a constraint

For more information on ALTER TABLE functionality and the complete syntax, see ALTER TABLE (Transact-SQL)

Schema-bound Dependency

Natively compiled stored procedures are required to be schema-bound, meaning they have a schema-bound dependency on the memory optimized tables they access, and the columns they reference. A schema-bound dependency is a relationship between two entities that prevents the referenced entity from being dropped or incompatibly modified as long as the referencing entity exists.

For example, if a schema-bound natively compiled stored procedure references a column c1 from table mytable, column c1 cannot be dropped. Similarly, if there is such a procedure with an INSERT statement without column list (e.g., INSERT INTO dbo.mytable VALUES (...)), then no column in the table can be dropped.

Logging of ALTER TABLE on memory-optimized tables

On a memory-optimized table, most ALTER TABLE scenarios now run in parallel and result in an optimization of writes to the transaction log. The optimization is achieved by only logging the metadata changes to the transaction log. However, the following ALTER TABLE operations run single-threaded and are not log-optimized.

The single-threaded operation in this case would log the entire content of the altered table to the transaction log. A list of single-threaded operations follows:

• Alter or add a column to use a large object (LOB) type: nvarchar(max), varchar(max), or varbinary(max).

• Add or drop a COLUMNSTORE index.

• Almost anything that affects an off-row column.

  • Cause an on-row column to move off-row.
  • Cause an off-row column to move on-row.
  • Create a new off-row column.
  • Exception: Lengthening an already off-row column is logged in the optimized way.

Examples

The following example alters the bucket count of an existing hash index. This rebuilds the hash index with the new bucket count while other properties of the hash index remain the same.

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

The following example adds a column with a NOT NULL constraint and with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

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

The following example adds a primary key constraint to an existing column.

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

The following example removes an index.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       DROP INDEX ix_ModifiedDate;  
GO

The following example adds an index.

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

The following example adds multiple columns, with an index and constraints.

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  

See Also

Memory-Optimized Tables