SQL Server table scan when using SELECT *

Question

I am working with SQL Server 2019 and have a TVF that retrieves products based on a list of categories. However, I noticed that when I use SELECT *, the query results in a table scan, whereas selecting specific indexed columns results in an index seek, leading to better performance.

I need to keep using SELECT * because this function is used in multiple areas where different columns are needed. Additionally, forcing an index is not an option because the results might be sorted later outside the TVF, and forcing an index could degrade performance in such cases.

Reproducible example

I have a Products table with an index on CategoryId:

CREATE TABLE Products ( ProductId INT PRIMARY KEY, Name NVARCHAR(100), CategoryId CHAR(1), Price DECIMAL(10,2), CreatedAt DATETIME ); CREATE INDEX IX_Products_CategoryId ON Products (CategoryId); 

I also have a table type to pass category filters:

CREATE TYPE Categories AS TABLE ( CategoryId CHAR(1) PRIMARY KEY ); 

And my TVF query is:

DECLARE @Categories Categories; INSERT INTO @Categories (CategoryId) VALUES ('B'), ('C'); SELECT TOP 10 * FROM Products WHERE CategoryId IN (SELECT CategoryId FROM @Categories); 

Observations

• If I select specific columns covered by the index, I get an Index Seek (good performance):

SELECT TOP 10 ProductId, CategoryId FROM Products WHERE CategoryId IN (SELECT CategoryId FROM @Categories); 

• If I do SELECT * (all columns), I get a table scan (bad performance):

SELECT TOP 10 * FROM Products WHERE CategoryId IN (SELECT CategoryId FROM @Categories); 

Constraints & requirements

1. I cannot avoid SELECT * because different usages of this TVF require different columns
2. I cannot force an index because it may negatively impact performance when sorting is applied outside the TVF.
3. Sorting the data inside the TVF is not an option since sorting is handled by the caller
4. The function must remain a TVF as it is used in multiple queries with additional filters applied

Questions

• How can I modify the query or structure to ensure index seek behavior while keeping SELECT *?

• Are there any best practices for handling this inside a TVF without forcing an index?

Execution Plan

Attaching execution plan (generated with small amount of data):

SELECT *:

SELECT CategoryId:

Any insights or alternative approaches would be greatly appreciated!

Answer 1

This is a normal behaviour. Look at the cost of the plan and, for both queries, you can compare in forcing the other alternative...

SELECT TOP 10 ProductId, CategoryId FROM Products WITH(INDEX(???)) --> put then PK index name in place of ??? WHERE CategoryId IN (SELECT CategoryId FROM @Categories); 

and :

SELECT TOP 10 * FROM Products WITH(INDEX(IX_Products_CategoryId )) WHERE CategoryId IN (SELECT CategoryId FROM @Categories); 

Compare the cost and you will see that MS Query Engine do a good job because if the index has not all the columns into the definition, a join is required between the index and the table to get all extra columns... This cost a lot and table scan is often a good choice to avoid this join

Answer 2

Interestingly, if the query is written in this form, there should be an index scan, then a Key Lookup(Index seek) and Join.
If the selection is IN(...) it gives a small number of rows in the output(for example - 10 rows), it should be better this way without hints.
Key Lookup for 10 rows - an inexpensive operation, no matter how the rows are scattered. Maximum of 10 readings))

select p.* from( SELECT TOP 10 ProductId, CategoryId FROM Products p2 WHERE CategoryId IN (SELECT CategoryId FROM @Categories) )filt inner join Products p on p.ProductId=filt.ProductId and p.CategoryId=filt.CategoryId ;