Memory Optimized table - Adding hash index with included fields

365 views Asked by At

A number of indexes were dropped from a SQL Server 2017 memory-optimized table during a change a couple of months ago. Unfortunately it appears the source code for the original table is not available (NB. I've only become involved after the event) and all I've got is a list of the indexes that were on the table and the fields that were in each, but not the actual commands to create them.

I've created most of the indexes, but one of them includes included columns. I haven't been able to find the correct syntax to create a hash index on a SQL Server memory-optimized table that has included columns.

I've tried various commands similar to:

alter table TableName  
    add index IndexName  
    hash (Col1, Col2)
    include (Col3, Col4)
    with (bucket_count = 1048576);

For that command, SSMS is giving me a red-line for the open parenthesis after the include and the mouse-over hint is

Incorrect syntax near '('. Expecting ID, QUOTED_ID, STRING or TEXT_LEX.

I'm not certain just what it's looking for there: I've tried various things, such as the column_id and the column name as a string, but I haven't found the right syntax for the command.

I've also had a hunt online but I haven't found any link that covers creating an index on a memory-optimized table with included column(s).

Thanks in advance.

0

There are 0 answers