does partition elimination? 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 &lt;&gt; 1000


On NCI Seek, when I hover my mouse on it, I can see &quot;Actual Partition Count&quot; 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?
&lt;&gt; 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.
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 &lt;&gt; 1000 does not mean &quot;everything under 1000&quot;, 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 &lt;= 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 &lt;&gt; 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;