To create a memory-optimized table in Microsoft SQL Server, you can use the CREATE TABLE statement along with the MEMORY_OPTIMIZED option. Memory-optimized tables are designed to be stored in memory, which allows for high-speed data access and is beneficial for high-performance applications.
Here is the general syntax for creating a memory-optimized table:
CREATE TABLE [schema_name].[table_name] (
[column_name1] [data_type1] [NULL | NOT NULL],
[column_name2] [data_type2] [NULL | NOT NULL],
...
[primary key constraint],
...
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = [SCHEMA_AND_DATA | SCHEMA_ONLY]);
Parameters:
- schema_name: The name of the schema where the table will be created (e.g., dbo).
- table_name: The name of the new memory-optimized table.
- column_name: The name of each column in the table.
- data_type: The type of data that each column will hold (e.g., INT, NVARCHAR, etc.).
- NULL | NOT NULL: Specifies whether the column can accept null values.
- primary key constraint: You can define a primary key or other constraints for the table.
- MEMORY_OPTIMIZED = ON: This option indicates that the table is memory-optimized.
- DURABILITY: Use SCHEMA_AND_DATA to ensure that both the schema and data are durable. Use SCHEMA_ONLY to create a table which does not persist data in case of a crash.
Example:
CREATE TABLE dbo.MyMemoryOptimizedTable (
Id INT PRIMARY KEY NONCLUSTERED,
Name NVARCHAR(100) NOT NULL,
CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME()
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Notes:
1. When creating a memory-optimized table, the PRIMARY KEY must be defined as NONCLUSTERED.
2. You need to have the Database in the appropriate compatibility level (SQL Server 2014 or later) and it must be configured to allow memory-optimized features.
3. Make sure that the MEMORY_OPTIMIZED option is supported and that your SQL Server instance has the necessary resources allocated for memory-optimized objects.
4. Once created, memory-optimized tables cannot be altered to change their memory-optimized status.
Step-by-step Example:
1. Create a memory-optimized filegroup:
Before creating memory-optimized tables, make sure you have a memory-optimized filegroup:
ALTER DATABASE YourDatabaseName
ADD FILEGROUP [YourMemoryOptimizedFilegroupName] CONTAINS MEMORY_OPTIMIZED_DATA;
2. Create the memory-optimized table:
Use the CREATE TABLE statement as shown in the previous sections to create your memory-optimized table.