I have a [dbo].[SalesBudgetMonths] table with a columnstore index on it.

When I join to a physical table dbo.Sellers on the indexed column [Seller] like this:

;WITH s1 AS 
(
    SELECT 232 AS Id
)
SELECT
    sbm.[Type]
FROM 
    [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
JOIN 
    dbo.Sellers s ON s.Id = sbm.[Seller] 
-- JOIN s1 ON s1.Id = sbm.[Seller]
GROUP BY 
    sbm.[Type];

everything is ok. Execution plan shows index scan on both tables.

But if I join to a CTE oraz declared table @ ...

;WITH s1 AS 
(
     SELECT 232 AS Id
)
SELECT
    sbm.[Type]
FROM 
    [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
-- JOIN dbo.Sellers s ON s.Id = sbm.[Seller] 
JOIN 
    s1 ON s1.Id = sbm.[Seller]
GROUP BY 
    sbm.[Type];

Management Studio says the index is missing.

1. Why is this happening? Even if it points to index by WITH (INDEX(TestIndex))
2. Is there a solution to this?
3. Should I not worry about this type of hints as long as the current execution plan shows that the seek/scan index is performed on the index and not on the table data.

Edit: Index definition. Generally, all columns of this table are in the same order as in the table.

CREATE NONCLUSTERED COLUMNSTORE INDEX [TestIndex] ON [dbo].[SalesBudgetMonths]
( 
       [Year]
      ,[Currency]
      ,[Department]
      ,[BudgetType]
      ,[Business]
      ,[Ranking]
      ,[Seller]
      ,[CustomerService]
      ,[Supplier]
      ,[SupplierGroup]
      ,[Customer]
      ,[CustomerGroup]
      ,[ItemShortCode]
      ,[Month]
      ,[Type]
      ,[Budget Year]
      ,[Sales]
      ,[Sales LY]
      ,[Budget Jan Year]
      ,[Sales Jan]
      ,[Sales LY Jan]
      ,[Budget Feb Year]
      ,[Sales Feb]
      ,[Sales LY Feb]
      ,[Budget Mar Year]
      ,[Sales Mar]
      ,[Sales LY Mar]
      ,[Budget Apr Year]
      ,[Sales Apr]
      ,[Sales LY Apr]
      ,[Budget May Year]
      ,[Sales May]
      ,[Sales LY May]
      ,[Budget Jun Year]
      ,[Sales Jun]
      ,[Sales LY Jun]
      ,[Budget Jul Year]
      ,[Sales Jul]
      ,[Sales LY Jul]
      ,[Budget Aug Year]
      ,[Sales Aug]
      ,[Sales LY Aug]
      ,[Budget Sep Year]
      ,[Sales Sep]
      ,[Sales LY Sep]
      ,[Budget Oct Year]
      ,[Sales Oct]
      ,[Sales LY Oct]
      ,[Budget Nov Year]
      ,[Sales Nov]
      ,[Sales LY Nov]
      ,[Budget Dec Year]
      ,[Sales Dec]
      ,[Sales LY Dec]
      ,[Budget Year YTD]
      ,[Sales YTD]
      ,[Sales LY YTD]
      ,[Budget Year YTD MONTH]
      ,[Sales YTD  MONTH]
      ,[Sales LY YTD  MONTH]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO

I have a [dbo].[SalesBudgetMonths] table with a columnstore index on it.

When I join to a physical table dbo.Sellers on the indexed column [Seller] like this:

;WITH s1 AS 
(
    SELECT 232 AS Id
)
SELECT
    sbm.[Type]
FROM 
    [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
JOIN 
    dbo.Sellers s ON s.Id = sbm.[Seller] 
-- JOIN s1 ON s1.Id = sbm.[Seller]
GROUP BY 
    sbm.[Type];

everything is ok. Execution plan shows index scan on both tables.

But if I join to a CTE oraz declared table @ ...

;WITH s1 AS 
(
     SELECT 232 AS Id
)
SELECT
    sbm.[Type]
FROM 
    [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
-- JOIN dbo.Sellers s ON s.Id = sbm.[Seller] 
JOIN 
    s1 ON s1.Id = sbm.[Seller]
GROUP BY 
    sbm.[Type];

Management Studio says the index is missing.

1. Why is this happening? Even if it points to index by WITH (INDEX(TestIndex))
2. Is there a solution to this?
3. Should I not worry about this type of hints as long as the current execution plan shows that the seek/scan index is performed on the index and not on the table data.

Edit: Index definition. Generally, all columns of this table are in the same order as in the table.

CREATE NONCLUSTERED COLUMNSTORE INDEX [TestIndex] ON [dbo].[SalesBudgetMonths]
( 
       [Year]
      ,[Currency]
      ,[Department]
      ,[BudgetType]
      ,[Business]
      ,[Ranking]
      ,[Seller]
      ,[CustomerService]
      ,[Supplier]
      ,[SupplierGroup]
      ,[Customer]
      ,[CustomerGroup]
      ,[ItemShortCode]
      ,[Month]
      ,[Type]
      ,[Budget Year]
      ,[Sales]
      ,[Sales LY]
      ,[Budget Jan Year]
      ,[Sales Jan]
      ,[Sales LY Jan]
      ,[Budget Feb Year]
      ,[Sales Feb]
      ,[Sales LY Feb]
      ,[Budget Mar Year]
      ,[Sales Mar]
      ,[Sales LY Mar]
      ,[Budget Apr Year]
      ,[Sales Apr]
      ,[Sales LY Apr]
      ,[Budget May Year]
      ,[Sales May]
      ,[Sales LY May]
      ,[Budget Jun Year]
      ,[Sales Jun]
      ,[Sales LY Jun]
      ,[Budget Jul Year]
      ,[Sales Jul]
      ,[Sales LY Jul]
      ,[Budget Aug Year]
      ,[Sales Aug]
      ,[Sales LY Aug]
      ,[Budget Sep Year]
      ,[Sales Sep]
      ,[Sales LY Sep]
      ,[Budget Oct Year]
      ,[Sales Oct]
      ,[Sales LY Oct]
      ,[Budget Nov Year]
      ,[Sales Nov]
      ,[Sales LY Nov]
      ,[Budget Dec Year]
      ,[Sales Dec]
      ,[Sales LY Dec]
      ,[Budget Year YTD]
      ,[Sales YTD]
      ,[Sales LY YTD]
      ,[Budget Year YTD MONTH]
      ,[Sales YTD  MONTH]
      ,[Sales LY YTD  MONTH]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO

• sql-server
• indexing
• columnstore
• sql-server-2022

Share Improve this question Follow edited Mar 23 at 8:15 Dominik asked Mar 22 at 20:05 DominikDominik 20733 silver badges1313 bronze badges 2

• 1) Please show us the index definition. 2) Why are you using an index hint? SQL Server is usually better than you at choosing the correct index for best performance. – Dale K Commented Mar 23 at 0:02
• 1) Please check my edit. 2) Yes, i know that. This is just for example. If I don't use this hint, the result is the same. – Dominik Commented Mar 23 at 8:16

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Why is this happening? Even if it points to index by WITH (INDEX(TestIndex))

Because SQL Server thinks it would be cheaper to seek a BTree index on Seller (probably with Type included) than to scan the whole columnstore index. You can't seek to a single row in a columnstore, but it's built to be scanned very quickly. But it's still cheaper to seek a BTree if you're only looking for one value.

Is there a solution to this?

There's not a problem. Just understand your query patterns and make good (not perfect) choices for your indexes.

Should I not worry about this type of hints as long as the current execution plan shows that the seek/scan index is performed on the index and not on the table data.

There's no real difference between tables and indexes in SQL Server. Best to think of everything as is an index, including heaps and columnstores. You just need to pick the right indexes.