Sample Header Ad - 728x90

Why aggregate operator used after unique index scan

15 votes
1 answer
417 views
I have a table with a unique index filtered for non-nullable values. In the query plan there is use of distinct. Is there a reason for this? USE tempdb CREATE TABLE T1( Id INT NOT NULL IDENTITY PRIMARY KEY ,F1 INT , F2 INT ) go CREATE UNIQUE NONCLUSTERED INDEX UK_T1 ON T1 (F1,F2) WHERE F1 IS NOT NULL AND F2 IS NOT NULL GO INSERT INTO T1(f1,F2) VALUES(1,1),(1,2),(2,1) SELECT DISTINCT F1,F2 FROM T1 WHERE F1 IS NOT NULL AND F2 IS NOT NULL SELECT F1,F2 FROM T1 WHERE F1 IS NOT NULL AND F2 IS NOT NULL query plan : enter image description here
Asked by mordechai (253 rep)
Aug 7, 2014, 09:49 AM
Last activity: Jul 1, 2025, 05:34 PM