0

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.

4
  • I think the documentation you are relying on may be incomplete or incorrect. A hash index on a memory-optimized table automatically includes all columns on the table. Even if it didn’t, hash indexes are designed for individual exact matches, so the benefit of included columns (avoid excessive lookups) just isn’t relevant. Commented Jul 14, 2022 at 2:44
  • The syntax for hash indexes doesn't have include, see learn.microsoft.com/en-us/sql/t-sql/statements/… INDEX index_name {[ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count) | ... Commented Jul 14, 2022 at 10:09
  • The included columns you mention are likely just artifacts of a prior disk-tabled table index. Included columns are not allowed on memory-optimized indexes because the row locator of a hash index is the address of the corresponding in-memory row so retrieving other columns does not require a "lookup" as do disk-based tables.
    – Dan Guzman
    Commented Jul 14, 2022 at 10:32
  • Thanks all. I thought it might be that included columns weren't applicable for memory-optimized tables, but when SSMS didn't complain about the include keyword I wasn't certain. I shouldn't know better than to rely on SSMS for "support". Obsolete documentation doesn't help either.
    – GlennD
    Commented Jul 15, 2022 at 4:35

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.