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.