Syntax for createing filtered Index

[Olaf Doschke]

This is taken from the Filtered Index Design Guidelines:

CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (Name, ListPrice)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

It raises Error 156, Level 15, Status 1, Line 3: Incorrect Syntax Near the keyword 'WHERE'.

According to the Syntax for CREATE INDEX it's okay. What could be the problem?

(SQL Server 2005)

 

[gmmastros]

I think you were looking at documentation for SQL 2008.

SQL2008:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

SQL2005:

http://msdn.microsoft.com/en-us/library/ms188783(SQL.90).aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

ouch, this seems to be a new SQL2008 feature.

http://msdn.microsoft.com/de-de/library/ms188783(SQL.90).aspx

Does not include a WHERE clause.

Is there another way to handle the following case?

I have a field named SwitchOff (bit) in each table, I want to exclude records from an Index, which are set SwitchOff = 1, because they count as if deleted but are still kept for several reasons.

imagine a astructure (ID, NameField, SwitchOff) I want to be able to reenter a Namefield value that exists with SwitchOff = 1, although there is a UNIQUE Index on NameField.

It would not be right to simply set SwitchOff = 0 and reactivate the old record in a more complex structure.

Bye, Olaf.

 

[gmmastros]

Even with a filtered index (which you can't do until you upgrade to sql 2008), you still won't be able to add another row with the same NameField because of the UNIQUE index on it.

Originally I was thinking that you could create a view (with a where clause) and then index the view. I think that would essentially give you the same thing (but could be wrong here).

Perhaps you could modify your UNIQUE index to include the SwitchOff column. Then, the combination of NameField and SwitchOff would be unique. This will only work to have 2 rows with the same name (one with SwitchOff = 1 and the other 0).

If you decide to modify the index, make sure the NameField appears first on the list of columns. It's important for performance.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Thanks George,

a View with such an index would not be much help, I'd rather put the rule to disallow duplicate values into buiseness rules in a middle tier . Adding the SwitchOff field to the index is just a slight help. I'll see if I simply manage to set SwitchOff=0 again and reinit some other fields like the ID for example. It would break the archive purpose, though.

By the way, the help on CREATE INDEX in the WHERE explaination tells: "For UNIQUE indexes, only the selected rows must have unique index values", that would ideally fit my needs. So upgrading to SQL2008 will help to do that in the future.

Could I perhaps index on a CLR user defined colum and use a method to return NULL, if SwitchOff = 1 and configure the index to accept is any number of NULL values, only non-NULL values must be unique?

Bye, Olaf.

 

[Olaf Doschke]

I see that NULL is not an option too. Also double NULL values are considered duplicates.

Bye, Olaf.