WHERE clause doesn't work in CREATE INDEX

  • This is in regards to Indexes, Level 7, Filters:

    The command I am specifically trying to run is:

    CREATE INDEX FI_SpecialOfferID

    ON Sales.SalesOrderDetail (SpecialOfferID)

    WHERE SpecialOfferID;

    Its purpose is to screen out records where SpecialOfferID = 1, and my first thought when I first encountered it was that it was missing part of the clause. So after the first expected failure, I changed it to

    CREATE INDEX FI_SpecialOfferID

    ON Sales.SalesOrderDetail (SpecialOfferID)

    WHERE SpecialOfferID <> 1;

    But that failed with the same error:

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'WHERE'.

    I tried a couple other things, like specifying the full datapath in the WHERE clause, and nothing works. Also, I was working in Sql 2005, as directed by the series. Was it possible this syntax belongs in Sql 2008? So I tried it there and got a different error message:

    Msg 4104, Level 16, State 1, Line 3

    The multi-part identifier "Sales.SalesOrderDetail.SpecialOfferID" could not be bound.

    Which at least makes it look like a data error and not a syntax error.

    Any clues?

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply