79529357

Date: 2025-03-23 17:34:08
Score: 1.5
Natty:
Report link

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.

Reasons:
  • Blacklisted phrase (3): Is there a solution
  • RegEx Blacklisted phrase (0.5): Why is this
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Contains question mark (0.5):
  • Starts with a question (0.5): Why is this
  • High reputation (-2):
Posted by: David Browne - Microsoft