Enable In-Memory OLTP

-- The below scipt enables the use of In-Memory OLTP in the current database,
--   provided it is supported in the edition / pricing tier of the database.
-- It does the following:
-- 1. Validate that In-Memory OLTP is supported.
-- 2. In SQL Server, it will add a MEMORY_OPTIMIZED_DATA filegroup to the database
--    and create a container within the filegroup in the default data folder.
-- 3. Change the database compatibility level to 130 (needed for parallel queries
--    and auto-update of statistics).
-- 4. Enables the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to avoid the
--    need to use the WITH (SNAPSHOT) hint for ad hoc queries accessing memory-optimized
--    tables.
--
-- Applies To: SQL Server 2016 (or higher); Azure SQL Database
-- Author: Jos de Bruijn (Microsoft)
-- Last Updated: 2016-05-02

set nocount on;

set xact_abort on;

-- 1. validate that In-Memory OLTP is supported
if serverproperty(N'IsXTPSupported') = 0
begin
    print N'Error: In-Memory OLTP is not supported for this server edition or database pricing tier.';
end;

if db_id() < 5
begin
    print N'Error: In-Memory OLTP is not supported in system databases. Connect to a user database.';
end;
else
begin
    begin try;
        -- 2. add MEMORY_OPTIMIZED_DATA filegroup when not using Azure SQL DB
        if serverproperty('EngineEdition') <> 5
        begin
            declare @SQLDataFolder nvarchar(max) = cast(serverproperty('InstanceDefaultDataPath') as nvarchar(max));
            declare @MODName nvarchar(max) = db_name() + N'_mod';
            declare @MemoryOptimizedFilegroupFolder nvarchar(max) = @SQLDataFolder + @MODName;
            declare @SQL nvarchar(max) = N'';

            -- add filegroup
            if not exists (select 1 from sys.filegroups where type = N'FX')
            begin
                set @SQL = N'
ALTER DATABASE CURRENT
ADD FILEGROUP ' + quotename(@MODName) + N' CONTAINS MEMORY_OPTIMIZED_DATA;';

                execute (@SQL);
            end;

            -- add container in the filegroup
            if not exists (
                select *
                from sys.database_files
                where data_space_id in (select data_space_id from sys.filegroups where type = N'FX')
            )
            begin
                set @SQL = N'
ALTER DATABASE CURRENT
ADD FILE (name = N''' + @MODName + N''', filename = ''' + @MemoryOptimizedFilegroupFolder + N''')
TO FILEGROUP ' + quotename(@MODName);

                execute (@SQL);
            end;
        end;

        -- 3. set compat level to 130 if it is lower
        if (select compatibility_level from sys.databases where database_id = db_id()) < 130
            alter database current
                set compatibility_level = 130;

        -- 4. enable MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT for the database
        alter database current
            set memory_optimized_elevate_to_snapshot=on;
    end try
    begin catch
        print N'Error enabling In-Memory OLTP';

        if xact_state() <> 0
            rollback;

        throw;
    end catch;
end;