Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Aug 7 at 11:14

This question came from our site for professional and enthusiast programmers.

1  
Looks like an optimizer bug. I have seen other quirks with filtered indexes. The optimizer sometimes cannot derive trivial information from a filtered index. –  usr Aug 7 at 10:08
    
Change the ddl to CREATE TABLE T1( Id INT NOT NULL IDENTITY PRIMARY KEY ,F1 INT NOT NULL, F2 INT NOT NULL ) –  Jaugar Chang Aug 7 at 10:44
1  
@Jauger I'm not sure how that will help - the OP obviously wants a filtered index because they're going to have a mix of rows where sometimes those columns are populated and sometimes they're NULL. –  Aaron Bertrand Aug 7 at 13:25

1 Answer 1

up vote 10 down vote accepted

This is a known SQL Server query optimizer limitation. It has been reported to Microsoft in this Connect item. There are additional consequences of this limitation, including some that I wrote about in this article.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.