-- create a database with a memory-optimized filegroup and a container.
CREATE DATABASE TEST_MEMORY_OPTIMIZED
GO
ALTER DATABASE TEST_MEMORY_OPTIMIZED ADD FILE (name='FILE_MEMORY_OPTIMIZED', filename='C:\DATA\FILE_MEMORY_OPTIMIZED') TO FILEGROUP FG_MEMORY_OPTIMIZED
ALTER DATABASE TEST_MEMORY_OPTIMIZED SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
-- create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed
-- Hash index has better performance than the normal nonclustered index, but needs to estimate the number of hash buckets. The number should be close enough to the number of table rows, otherwise there will be too much hash collisions when creating indexing and thus downgrade the performance a lot.
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
-- this is useful for staging data
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