I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?
Demo data:
CREATE TABLE #Docs (
ID INT NOT NULL
,DocType TINYINT NOT NULL
);
CREATE TABLE #DocsItems (
IDDocs INT NOT NULL
,Amount NUMERIC(19,6)
);
INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);
Subject query:
SELECT
-- expression
SumAmount = CASE
WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
END
FROM #Docs D
WHERE D.DocType = 1 -- so CASE condition evaluates to False
If I rewrite the query (on purpose) to:
SELECT
-- expression
SumAmount = CASE
WHEN 2 = 1 /* rewrite*/ THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
END
FROM #Docs D
WHERE D.DocType = 1
The plan goes the way it should / could / would in the original query: