@McNets does not matter in this case, assume barcode is unique on the table and there are no records that actually match those predicates

I get what you are saying but for this example it does not matter as there are ZERO records that match the WHERE clause, so there should be no records returned by either statement

I see that @tableID is NULL, but is part of an equality comparison. Does the discrepancy still occur if you remove that part of the queries?

It does not, but select SESSIONPROPERTY('ANSI_NULLS') returns 1, indicating that ANSI_NULLS is correctly on for the session?

It looks like NULL is the current path to investigate. Could you provide SELECT COUNT(*) results when you only have the TableID filter?

0 rows, the second statement switches to using an alternative nci rather than the cci with only the tableId filter. If I force the cci using a hint, still 0?

Have you tried TableID IS NULL instead of TableID = @tableId?

"The filter predicate uses simple comparison logic. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead." from docs.microsoft.com/en-us/sql/t-sql/statements/…

So the situation only occurs when the @tableID predicate is applied in a key-lookup situation [I actually see a key lookup when I try to repro, not a scan]. I think this is an interesting problem, but did not get a repro. Could you provide a MCVE?

What CHECKDB options did you choose? Can you check the specific table with all optional checks enabled?

@Forrest, I am unable to repro it when using the same setup against a dummy table, which is what leads me to believe it is something about the specific setup of this table, but nothing in the table definition seems obvious to me? Martin Smith; I just ran the default checkdb, will run again and share the results.

@Uberzen1 The comment section is getting a bit long and starting to distract from the question. I'm going to see if I can get someone to move it to a chat room. Would you mind editing into your question a few details about what you've tested with NULLs?

@Uberzen1 Now that I won't be exploding the comment section, I have a few more ideas. First, what happens if you script out the table and indexes and copy data?

Other idea: what happens if you rebuild the CCI?

@Forrest I've spent all day working on an MVCE for this, which I now have! Now about to post an answer to my question with script to repro this. Long story short; it is a deleted column

fascinating!

thank you very much for following up on it

I look forward to reading and testing

And thank you too for your help!