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.
hash
indexes doesn't haveinclude
, see learn.microsoft.com/en-us/sql/t-sql/statements/…INDEX index_name {[ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count) | ...