ScottPletcher (11/30/2015)
In more detail, index "FDX_spG_NU_QA_V12" does not contain at least one of the columns:MC.col1 / MC.col2 / MC.col3
That won't cause the reported error. He said it's a filtered index, but the query has no filter and hence can't match a filtered index. Hinting a non-covering index doesn't throw that error, it just means that the optimiser adds a key lookup to the plan.
CREATE TABLE t1 (
Col1 INT,
Col2 INT
)
CREATE INDEX idx_Noncovering ON t1 (Col1)
CREATE INDEX idx_Filtered ON t1 (Col1) WHERE col1 < 0
SELECT Col1, Col2 FROM dbo.t1 WITH (INDEX (idx_Noncovering)) -- runs fine
SELECT Col1, Col2 FROM dbo.t1 WITH (INDEX (idx_Filtered)) -- throws error 8622
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability