We have a partitioned tables partitioned on STATUS Column using below partitioning function. So a table will have total two partitions and both are going to different filegroups as per partition scheme.
CREATE PARTITION FUNCTION PART_FN_STATUS(INT) AS RANGE RIGHT FOR VALUES (1000)
NOTE: As per buisness logic, status cannot be greater than 1000. 1000 is Max.
We have a Non Clustered Index on Status Column as well which is an Aligned Partitioned Index, i.e partitioned on Status Column.
When I run below query and observe actual execution plan, I have below questions:
SELECT COUNT(*) FROM ORDERS WHERE STATUS <> 1000
- On NCI Seek, when I hover my mouse on it, I can see "Actual Partition Count" as 2. So why it is not doing partition elimination?
- On same tool tip, when i checked estimated / actual number of rows read, it is actually the count of 1 partition. So it looks like partition elimination happened?
- <> is a Non-Sargable Operator, so how can a SEEK happen on a NCI?
- Can a Non-Sargable Operator do a Partition Elimination?
Any help would be appreciated.
Best Answer
Yes, but...
Your query is asking a bad question. As-is, the optimizer has no idea if the value 1001 exists in the table. The predicate
<> 1000
does not mean "everything under 1000", unless you give it more information.Setup
Fix One: A Constraint
A check constraint can help, but...
You'll need to beware of getting a trivial plan/simple parameterization. If your literal value is substituted with a parameter via simple parameterization, you'll need to add something to your query to avoid that happening, like this:
Fix Two: Hacking The Function
If you feel like directing the optimizer manually to a partition, you can query the table like this to avoid hitting unnecessary partitions: