CODEWITHSUNDEEP
sqlsql-servert-sql
Mohammed Ali
Mohammed Ali

Reputation: 1185

Alter Table to make MEMORY_OPTIMIZED=ON ERROR SQL Server 2017

I need to alter table to add MEMORY_OPTIMIZED = ON , DURABILITY =SCHEMA_ONLY The syntax according to documentation is like this :

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
<table_option>
}

where <table_option> are :

<table_option> ::=
{
    MEMORY_OPTIMIZED = ON
  | DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}
  | SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name
        [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]
}

My code is :

 ALTER TABLE memtypes 
 MEMORY_OPTIMIZED = ON , DURABILITY =SCHEMA_ONLY;

but I get this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MEMORY_OPTIMIZED'.

Any help please ?

Upvotes: 4

Views: 7082

Answers (1)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

the process that is required to migrate the disk tables to In-Memory OLTP tables is not straightforward and requires analysis before you can make a final decision for migration. However, the benefits that an In-Memory OLTP table delivers is worth your effort

Suppose that we have a disk based table as follow:

CREATE TABLE <Disk based table name>
(
    id INT Primary Key,
    fname VARCHAR(10)
);
GO

Lets go through the process step by step.

In order to have a in memory optimised table, your database need to have a memory optimised file group and a file assigned to it. To do so follow these steps, or if you have already set it up, simple ignore them:

--add the filegroup to database named TEST
ALTER DATABASE <Your Database>
ADD FILEGROUP <Filegroup Name>
CONTAINS MEMORY_OPTIMIZED_DATA;

--Add and assign a file to filegroup
ALTER DATABASE <Your Database>
ADD FILE
    (
        NAME = <File Name>,
        FILENAME = <File store location>
    )
TO FILEGROUP <Filegroup Name>;

--Simply test the database to check if it now supports in memory optimised tables or not?
USE <Your Database>;
GO
SELECT g.name,
       g.type_desc,
       f.physical_name
FROM sys.filegroups g
    JOIN sys.database_files f
        ON g.data_space_id = f.data_space_id
WHERE g.type = 'FX'
      AND f.type = 2;

After those steps, you need to create a NEW memry optimized table and migrate the data from disk based table to it. 

CREATE TABLE <In memory Table name>
(
    id INT,
    fname VARCHAR(10),
    CONSTRAINT PK_TEST_Memory_ID
        PRIMARY KEY NONCLUSTERED HASH (id) WITH(BUCKET_COUNT=1572864)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

INSERT INTO <In memory Table name>
SELECT *
FROM <Disk based table name>;

Read more about this migration in Red-Gate blog: https://www.red-gate.com/simple-talk/sql/database-administration/migrating-disk-based-table-memory-optimized-table-sql-server/

And also here is another useful GitHub: https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-in-memory-oltp-migration.md

Upvotes: 4

Related Questions