SQL Server - Nonclustered Index with Include on User Defined Table Type?

Question

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.

Answer 1

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.