Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

WHERE clause doesn't work in CREATE INDEX Expand / Collapse
Author
Message
Posted Friday, April 05, 2013 4:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 2:38 PM
Points: 38, Visits: 42
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?

Post #1439522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse