2

I was working with in-memory User Defined Table Types and got stuck on creating indexes on them.

Here's what I am trying to do:

CREATE TYPE dbo.CustomersTbl
AS TABLE  
( 
        customerID int NOT NULL,
        customerName nvarchar(100),
        customerAddress nvarchar(100),
        customerCity nvarchar(100),
        customerEmail nvarchar(100)

        INDEX IDX1 NONCLUSTERED (customerID) INCLUDE(customerName,customerAddress,customerCity)
)

    WITH  
    (MEMORY_OPTIMIZED = ON); 

This works fine if I do not include the INCLUDE part.

If this were a standard table I would create the index with the INCLUDE.

So my question, is there a way to create that index on the User Defined Table Type with the INCLUDE, or would there be a reason why I would not even want to? I am using these table types to create in-memory table variables.

I am currently using SQL Server 2017.

2

INCLUDE is not supported for memory-optimized tables or table variables. For these objects, the purpose of INCLUDE is moot, as all columns are available once you arrive at the row location in memory.

Memory-optimized indexes are inherently covering, see Guidelines for Memory-Optimized indexes

Disk-based indexes use row locators that are either a RID or a Clustered Index Key, and following those costs 1-4 Logical IOs, followed by locating the target row in the slot table on the page and going there. The row locator for an In-Memory index is a pointer. Following a pointer costs a single CPU instruction, and a main memory read, somewhere in the 100ns-1000ns range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.