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. 

What types of objects can synonyms reference in SQL

A synonym is a database object that provides an alternate name (alias) for another object, called the base object. Internally SQL Server stores only the object name string and it is not schema binding and not validation at creation time. Name resolution happens at runtime. It does NOT copy data or logic.

Read: Deferred name resolution in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2016/06/deferred-name-resolution-in-sql-server.html

Synonyms in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2016/11/synonyms-in-sql-server.html

synonyms work on below objects.

See the example

Tables:

For the demo here we are creating a table and inserting few records after that creating the synonyms.

CREATE TABLE dbo.Products (     ProductID INT PRIMARY KEY,     Name VARCHAR(50) );   INSERT INTO dbo.Products VALUES (1,'Laptop'),(2,'Mobile');   CREATE SYNONYM syn_Products FOR dbo.Products; GO

Query the synonym

View

Creating a view

CREATE VIEW dbo.vwProducts AS SELECT * FROM dbo.Products; GO CREATE SYNONYM syn_vwProducts FOR dbo.vwProducts;

 

See the data

 

SP:

Creating user defined SP

CREATE PROC dbo.usp_GetProducts AS SELECT * FROM dbo.Products; GO CREATE SYNONYM syn_GetProducts FOR dbo.usp_GetProducts;

See the result

Function

Creating Function

CREATE FUNCTION dbo.fn_CountProducts() RETURNS INT AS BEGIN     RETURN (SELECT COUNT(*) FROM dbo.Products); END; GO CREATE SYNONYM syn_CountProducts FOR dbo.fn_CountProducts;

See the result

Sequence

Creating a Sequence

CREATE SEQUENCE dbo.ProductSeq START WITH 100 INCREMENT BY 1; GO CREATE SYNONYM syn_ProductSeq FOR dbo.ProductSeq;

See the result.

SELECT NEXT VALUE FOR syn_ProductSeq;

Ooo! It is failed. SQL Server does NOT support synonyms for sequence objects directly. Indirectly it will support.

Here creating a SP which call the Sequence.

CREATE PROCEDURE dbo.GetNextProductID AS BEGIN     SELECT NEXT VALUE FOR dbo.ProductSeq AS NextProductID; END;

Now creating a synonym on the view

CREATE SYNONYM dbo. GetNextProductIDSyn FOR dbo. GetNextProductID;

Now we can query the sequence.

SQL Server does not allow synonyms directly on sequence objects. However, sequences can be exposed via wrapper views or stored procedures, and synonyms can reference those wrappers to provide indirection across schemas or databases.

creating synonyms on temp table

we cannot create synonyms

see the example

CREATE TABLE #Products (     ProductID INT PRIMARY KEY,     Name VARCHAR(50) );   INSERT INTO #Products VALUES (1,'Laptop'),(2,'Mobile');   CREATE SYNONYM syn_ProductsTemp FOR #Products; GO

 

Synonyms created successfully but it cannot run

Running synonym

In the same scope we can use it but if you want to use in other session then we can’t use.

Because the base object is temporary.

Why must Table-Valued Parameters be READONLY

This question asked by interviewer. Why must Table-Valued Parameters be READONLY. Let’s see why it is read only. Table-Valued Parameters are mandatory READONLY because SQL Server passes them as immutable, streaming row sets optimized for concurrency, plan stability, and minimal logging. Allowing writes would break pipelining, require locking and logging, and invalidate execution plan assumptions. If modification is required, the TVP must be copied into a temp table.

When you pass a TVP as below

DECLARE @MyTVP MyTableType;

Then SQL Server Materializes it in memory (or tempdb if large) and treats it like a structured input stream and shares it safely across execution contexts. It is NOT a normal table variable.

TVPs are passed as a serialized row set stream. SQL Server can pipeline rows into execution plan
to avoid locking and avoid logging. If writes were allowed then it breaks streaming and would require full materialization locking.

Concurrency safety: Multiple threads may read TVP simultaneously. If we allowing writes than it would cause the Race conditions and Latches/locks.

Let’s see the demo

Creating table type

CREATE TYPE dbo.OrderType AS TABLE (     OrderID INT,     Amount DECIMAL(10,2) );

Now creating SP to consume this TT.

CREATE PROCEDURE dbo.usp_TestTVP     @Orders dbo.OrderType READONLY AS BEGIN     SELECT * FROM @Orders; END;

SP created successfully.

Let’s try to remove READONLY  and then creating the SP.

CREATE PROCEDURE dbo.usp_TestTVP_RemRO     @Orders dbo.OrderType AS BEGIN     SELECT * FROM @Orders; END;

Getting an error.

SQL Server enforces it.

Let’s try to insert/update the TT inside the SP.

CREATE PROCEDURE dbo.usp_TestTVP_insert     @Orders dbo.OrderType READONLY AS BEGIN INSERT INTO @Orders VALUES (3, 100); SELECT * FROM @Orders; END;

Getting an error.

When we need to update the data of Table type first, we need to create temp table or variable table and store TT data into it and the update temp table or variable table.

Why order by clause not working properly on sql_variant column

ORDER BY on sql_variant is unreliable because SQL Server sorts by data type precedence first and value second, not by logical value. Mixed data types will always be grouped by their underlying type. sql_variant does NOT sort by actual stored value alone. It first sorts by data type precedence, then by value inside that type.

When we are ordering sql_variant it orders on below order

datetime > float > decimal > int > varchar > nvarchar > bit ...

let’s see the demo

here creating a table and inserting few records.

CREATE TABLE sql_variant_Puzzles_Demo (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );   INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (1)              -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(0)                -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (10)              -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(2)                -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (10.0)              -- float INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(2.0)                -- float INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('10')           -- varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2')            -- varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST('2024-01-01' AS date))--date INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST('2020-01-01' AS date)) --date INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2024-01-01') --varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2020-01-01') --varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST(1 as bit)) --bit INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST(0 as bit)) --bit

Table created and data inserted successfully.

See the data

Now ordering the data in descending order and see

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType')  as datatype FROM sql_variant_Puzzles_Demo order by DataValue desc

 

Not getting the correct sorting order.

Two rows look identical in result set but group by return two group

This question is asked by interviewer they show two table like below.

Both tables have same records, but when we are using the group by it return the differ result.

 

Why this happens.

GROUP BY on sql_variant considers both the underlying data type and the stored value.
Two values that display the same may belong to different base types, so SQL Server treats them as different groups.

Let’s see the demo.

Creating two table and inserting few records.

CREATE TABLE tbl1 (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );  CREATE TABLE tbl2 (     ID INT IDENTITY PRIMARY KEY,     DataValue int ); insert into tbl1 (DataValue) values (10)         -- int insert into tbl1 (DataValue) values ('10')      -- varchar insert into tbl1 (DataValue) values (CAST(10 AS bigint)) --bigint insert into tbl1 (DataValue) values (CAST('10' AS nvarchar(10))); --nvarchar  insert into tbl2 (DataValue) values (10), (10),(10),(10)

See the data

Now grouping the data

Main reason is the datatype of both table tbl1 data type is sql_variant which can story any data type but in the seconds table tbl2 has int so in this table we can only insert int data type.

Due to that it tbl1 return 2 rows (int + bigint) and (varchar +nvarchar) while tbl2 has only one datatype.

Two rows look identical in result set but distinct by return more rows than expected

This question is asked by interviewer they show two table like below.

Both tables have same records, but when we are using the distinct it return the differ result.

 

Why this happens.

DISTINCT on sql_variant does not deduplicate by displayed value. It compares the underlying data type and metadata as well, so values that appear identical but were stored using different base types remain distinct.

Let’s see the demo.

Creating two table and inserting few records.

CREATE TABLE tbl1 (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );   CREATE TABLE tbl2 (     ID INT IDENTITY PRIMARY KEY,     DataValue int );  insert into tbl1 (DataValue) values (10)         -- int insert into tbl1 (DataValue) values ('10')      -- varchar insert into tbl1 (DataValue) values (CAST(10 AS bigint)) --bigint insert into tbl1 (DataValue) values (CAST('10' AS nvarchar(10))); --nvarchar insert into tbl2 (DataValue) values (10), (10),(10),(10)

See the data

Now using distinct

Main reason is the datatype of both table tbl1 data type is sql_variant which can story any data type but in the seconds table tbl2 has int so in this table we can only insert int data type.

Due to that it tbl1 return 2 rows (int + bigint) and (varchar +nvarchar) while tbl2 has only one datatype.

Query returns zero rows but we can see the records with value in the table

This is an interview question. An interviewer shows the table like below

But when we are writing the query below, we are not getting any records why?

SELECT     ID,     DataValue FROM sql_variant_Puzzles_Demo where DataValue=100

 

What is the reason behind it.

Reason behind it is data type of the column name DataValue. Data type of this column is sql_variant, it can store different type of data. In the above example we are searching int value but there may be change to store the data 100 as varchar or nvarchar or char . due to that it is not returning any row.

Read more about sql_variant  : https://bageshkumarbagi-msbi.blogspot.com/2026/05/sqlvariantproperty-function-in-sql.html

SQL_VARIANT data type in SQL Server : https://bageshkumarbagi-msbi.blogspot.com/2026/05/sqlvariant-data-type-in-sql-server.html

Let’s see the demo.

Creating a table and inserting few records.

CREATE TABLE sql_variant_Puzzles_Demo (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );  INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES ('100');           INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES ('100.0');          INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES ('100');       INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES (500);       INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES (400);

Table created and data inserted successfully. Now see the records

These 100 data types are varchar.

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType') FROM sql_variant_Puzzles_Demo

When we are filtering with int it will not return any data.

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType') FROM sql_variant_Puzzles_Demo where DataValue=100

SQL_VARIANT_PROPERTY function in SQL Server

SQL_VARIANT_PROPERTY exposes the underlying data type and metadata stored inside a sql_variant value. Since sql_variant preserves its original base type, operations like DISTINCT, GROUP BY, ORDER BY, and indexing rely on this internal metadata rather than the displayed value. This is why visually identical values may behave differently.

It supports below properties

Property	Meaning
BaseType	Actual stored SQL data type
Precision	For numeric types
Scale	For numeric types
TotalBytes	Storage size
Collation	For character types
MaxLength	Length for strings

SQL_VARIANT_PROPERTY() returns metadata about the underlying base data type stored inside a sql_variant, such as its base type, precision, scale, and collation.

Because sql_variant doesn’t store just a value — it stores:

[ Base Data Type ] + [ Actual Value ] + [ Metadata ]

And SQL Server normally hides this. SQL_VARIANT_PROPERTY() exposes it.

 Syntex

SQL_VARIANT_PROPERTY ( expression , property )

 Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE Variant_Properties_Demo (     id int identity(1,1) primary key,               dataVal sql_variant );  insert into Variant_Properties_Demo(dataVal) values (10)                           -- int insert into Variant_Properties_Demo(dataVal) values (CAST(10 AS bigint))           -- bigint insert into Variant_Properties_Demo(dataVal) values (10.25)                        -- decimal/float insert into Variant_Properties_Demo(dataVal) values ('10')                         -- varchar insert into Variant_Properties_Demo(dataVal) values (N'10')                        -- nvarchar insert into Variant_Properties_Demo(dataVal) values (CAST('2024-01-01' AS datetime))--datetime;

Table created and inserted records successfully.

See the data and its property using this function.

select id, dataVal, SQL_VARIANT_PROPERTY(dataVal,'BaseType')  AS [BaseType], SQL_VARIANT_PROPERTY(dataVal,'Precision') AS [Precision], SQL_VARIANT_PROPERTY(dataVal,'Scale')     AS [Scale], SQL_VARIANT_PROPERTY(dataVal,'MaxLength') AS [MaxLength], SQL_VARIANT_PROPERTY(dataVal,'TotalBytes')AS [TotalBytes], SQL_VARIANT_PROPERTY(dataVal,'Collation') AS [Collation] from Variant_Properties_Demo