<> does partition elimination?

Question

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

1. 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?
2. 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?
3. <> is a Non-Sargable Operator, so how can a SEEK happen on a NCI?
4. Can a Non-Sargable Operator do a Partition Elimination?

Any help would be appreciated.

Answer 1

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

CREATE PARTITION FUNCTION 
    a_func(int) 
    AS RANGE RIGHT FOR VALUES (1000);
GO
CREATE PARTITION 
    SCHEME a_scheme 
    AS PARTITION a_func ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.a_table
(
    id int PRIMARY KEY
) ON a_scheme(id);
GO 

INSERT dbo.a_table(id)
    VALUES(999), (1000);
GO 

Fix One: A Constraint

A check constraint can help, but...

ALTER TABLE dbo.a_table 
    ADD CONSTRAINT ck_a_ck CHECK(id <= 1000);

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:

SELECT 
    a.*,
    $PARTITION.a_func(a.id) AS pid
FROM a_table AS a
WHERE a.id <> 1000
AND   1 = (SELECT 1);

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:

SELECT 
    a.*
FROM a_table AS a
CROSS APPLY
(   
    VALUES($PARTITION.a_func(a.id))
) AS c (pid)
WHERE c.pid = 1;