Indexes on Table Types

SQL Server allows limited indexing on table types only through PRIMARY KEY and UNIQUE constraints, which create underlying clustered and non-clustered indexes for each TVP instance. Arbitrary indexes are not supported because TVPs are designed as lightweight, immutable streaming rowsets with predictable memory usage and stable plan compilation. Additionally, TVPs historically lack full statistics, so even with indexes the optimizer may produce poor cardinality estimates. For large or complex workloads, best practice is to materialize TVP data into temp tables with full indexing and statistics. 

We can create only below inline indexes on TT

Ø  clustered Index

Ø  non clustered index

We CANNOT create normal CREATE INDEX statements.

Let’s see the demo

Creating cluster index

CREATE TYPE dbo.OrderTypePK AS TABLE (    OrderID INT PRIMARY KEY,    CustomerID INT,    Amount MONEY,    INDEX Pk_OrderID CLUSTERED(CustomerID) );

Creating non cluster index on TT

CREATE TYPE dbo.OrderTypeNC AS TABLE (    OrderID INT PRIMARY KEY ,    CustomerID INT,    Amount MONEY,    INDEX Pk_OrderID CLUSTERED(CustomerID),    INDEX IX_OrderType NONCLUSTERED( OrderID, CustomerID ) );

 Both TT created successfully.

We can not create index for TT using create index as below.

CREATE INDEX IX_Price ON dbo.OrderTypeNC(OrderID);

It failed because table types are metadata templates, not real tables.

When we are creating table type it create metadata and not required for storage. A metadata blueprint that SQL Server uses to instantiate memory-backed rowsets at runtime. Every time a TVP is passed. SQL Server creates an internal structure (in memory or tempdb) and applies the constraints + indexes defined in the type.