Error in creating a unique index

Question

Good day everyone. I was trying to make a unique index that would ignore null values. here are the four codes that I tried:

CREATE UNIQUE NONCLUSTERED INDEX [BidContractNumber_UNIQUE] ON [biddetails] (BidContractNumber) WHERE [biddetails].[BidContractNumber] IS NOT NULL;

CREATE UNIQUE NONCLUSTERED INDEX [BidContractNumber_UNIQUE] ON [biddetails] (BidContractNumber) WHERE NOT((BidContractNumber) IS NULL);

CREATE UNIQUE INDEX BidContractNumber_UNIQUE ON biddetails (BidContractNumber) WHERE [BidContractNumber] IS NOT NULL;

CREATE UNIQUE INDEX BidContractNumber_UNIQUE ON biddetails (BidContractNumber) WHERE [BidContractNumber] IS NOT NULL;

but I always get this message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'WHERE'.

Can someone help me in correcting this? Thanks..

P.S.

I'm new to SQL server and I was asked to migrate our database..

Answer 1

I don't believe filtered indexes where available on SQL server 2005, they were introduced in SQL server 2008.

(https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/)