June 8, 2013 at 7:25 am
--View Definition
CREATE VIEW MyView
AS
SELECT
CASE WHEN (Col2 > 0) THEN Col1 END,
Col2
FROM MyTable
-- My Problem
SELECT * FROM MyView WHERE Col1 = 123
Here, It doesnt use the Col1 index, but if i remove the CASE Statement in Col1 of View, then it is.
My Question: Is it the expected behaviour of SQL Server, if yes, may i know why so?
or where am i wrong.
Appreciating your helps always.
June 8, 2013 at 7:57 am
It's perfectly expected, a case statement is not a SARGable expression, so no index seek possible.
CREATE VIEW MyView
AS
SELECT
CASE WHEN (Col2 > 0) THEN Col1 END AS Col1,
Col2
FROM MyTable
The query
SELECT * FROM MyView WHERE Col1 = 123
Early in parsing, the name of the view is replaced by the defintion, so
SELECT * FROM (
SELECT
CASE WHEN (Col2 > 0) THEN Col1 END AS Col1,
Col2
FROM MyTable) MyView
WHERE Col1 = 123
This simplifies to
SELECT
CASE WHEN (Col2 > 0) THEN Col1 END AS Col1,
Col2
FROM MyTable
WHERE CASE WHEN (Col2 > 0) THEN Col1 END = 123
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply