Does SQL Server even look at a table when attached to a variable that returns false?

Question

If I attach table A to table B, like this ...

select A.* from A 
left outer join B on A.Id = B.aId and @param = 'someValue'

and @param are not equal to "someValue", does SQL Server even try to match records from table B, or is it smart enough to know that the condition will never be true?

Answer 1

• SQL generally does not guarantee a short circuit of the boolean statement .
• The query plan should work for any @param value because the plans are reused between execution

, , @param , , . , , . . , , .

, , (, , , ).

Answer 2

, , , :

CREATE PROCEDURE dbo.Test_Params
    @param1 INT
WITH RECOMPILE
AS
BEGIN
    SELECT
        o.object_id,
        c.object_id
    FROM
        sys.objects o
    LEFT OUTER JOIN sys.columns c ON
        c.object_id = o.object_id AND
        @param1 = 1
    OPTION
        (RECOMPILE)
END
GO


EXEC dbo.Test_Params 1
EXEC dbo.Test_Params 2

EXEC, , sys.columns , , . , .