Documentation

Starting with SQL Server 2016 (13.x), there is no limit on the size of memory-optimized tables, though the tables do need to fit in memory.

Memory tables in SQL Server are pretty close analogs to Redis

• Will use as much memory as possible to stick entire table into memory
• Backed by disk table or shadow file based table on disk (this is for recovery purpose of server restart which clears memory) can specify the durability to schema only or schema and data

Documentation and walkthroughs

• Outline documents
• Memory estimation of table size
• Limitations on memory tables
• Walkthrough and overall review of memory tables

Create the Database

This is done because at this time it is not possible to drop the MEMORY filegroup from a database once created, therefore keeping the database separate allows for the ability to delete the entire database to clear the filegroup

USE [model]
CREATE DATABASE DbCache
GO
USE [DbCache]
GO

Create the FileGroup for the Database

-- Add the file group
ALTER DATABASE DbCache 
ADD FILEGROUP DbCache_memgroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

-- Find the path of the Database just created and replace folder for memory backing store
DECLARE @memFilePath nvarchar(MAX);
SELECT @memFilePath = REPLACE(physical_name, '\DbCache.mdf', '\Aggregator_MEM')
FROM sys.database_files
WHERE [name] = 'DbCache'

DECLARE @dynSql nvarchar(MAX)
-- Dynamic sql because of the above lookup for the path
SET @dynSql = 'ALTER DATABASE DbCache 
ADD FILE (name=''DbCache_MEM'', filename=''' + @memFilePath + ''') -- This path may be different on the database you are using
TO FILEGROUP DbCache_memgroup'

EXECUTE sp_executesql @dynSql;
GO

Create the Memory Table

-- Create the table
DROP TABLE IF EXISTS [dbo].[FastCache]
CREATE TABLE [dbo].[FastCache]
(
    Id              INTEGER             NOT NULL    IDENTITY(1,1),
    BatchId.        UNIQUEIDENTIFIER    NOT NULL    DEFAULT(NEWID()),
    JsonContents    NVARCHAR(MAX)       NOT NULL,
    OverrideCleanup BIT                 NOT NULL    DEFAULT(0),
    Created         DATETIME2           NOT NULL    DEFAULT(SYSUTCDATETIME()),
    CONSTRAINT [PK_FastCache] PRIMARY KEY NONCLUSTERED ([Id] ASC),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
ALTER TABLE [dbo].[FastCache]
    ADD INDEX IX_FastCache_BatchId HASH ([BatchId]) WITH (BUCKET_COUNT = 131072)
GO

Add the following trigger

To mimic the Expires feature of Redis in an active manner as otherwise would need to create a scheduled job to do the same type of functionality as Redis

CREATE TRIGGER [dbo].[CacheExpiration]
	ON [dbo].[FastCache]
	WITH NATIVE_COMPILATION, SCHEMABINDING
	AFTER INSERT, UPDATE
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
	DELETE 
	FROM [dbo].[FastCache]
	WHERE DATEDIFF(hh, [Created], SYSUTCDATETIME()) > 0
	AND [OverrideCleanup] = 0
END