Will SQL Server 2008 optimise out a join if not needed?

In the following query, the join to table title_deed is only required for the title_deed_no search criterion, and no fields are selected from it. If the @titleDeedNumber parameter is null, will a join still be performed? How much difference would this make to performance anyway? I am trying 开发者_开发技巧to replace dynamic generation of a big ugly T-SQL string, and the code that does that only adds a join if a title_deed_no value is supplied.

DECLARE @registrarId nchar(1)
DECLARE @titleDeedNumber nvarchar(50)

SELECT TOP 100
        vw.*
FROM    ACC.dbo.vw_Property_Nad vw
        INNER JOIN title_deed td ON ( @titleDeedNumber IS NULL )
                                    OR ( td.Prop_ID = vw.prop_id )
WHERE   vw.Prop_ID IS NOT NULL
        AND Registrar = isnull(@registrarId, vw.Registrar)
        AND td.title_deed_no = isnull(@titleDeedNumber, td.title_deed_no)

I have tried examning the execution plan, but it is just too busy with joins from the vw_Property_Nad view, and I'd rather get an expert opinion anyway.

In this case, probably not.

You're better off with:

• an EXISTS not JOIN (ie semi-join not equi-join)
• (vw.Registrar = @registrarId OR @registrarId IS NULL)
• ditto for td.title_deed_no in the EXISTS

Also

• TOP without ORDER BY is useless
• Your JOIN condition implies CROSS JOIN -> you'd need DISTINCT to get it working
• vw implies view -> the query is more complex than what you expect. A view is a macro that expands

I'd also consider an IF or UNION ALL

SELECT
        vw.*
FROM    ACC.dbo.vw_Property_Nad vw
WHERE   vw.Prop_ID IS NOT NULL
        AND (vw.Registrar = @registrarId OR @registrarId IS NULL)
        AND @titleDeedNumber IS NULL
UNION ALL
SELECT
        vw.*
FROM    ACC.dbo.vw_Property_Nad vw
WHERE   vw.Prop_ID IS NOT NULL
        AND (vw.Registrar = @registrarId OR @registrarId IS NULL)
        AND @titleDeedNumber IS NOT NULL
        AND EXISTS (...)
ORDER BY something

I'm not sure that you'd want the join to be optimized away. This would mean that you would have at least two different plans depending on the parameter, which means it won't be optimal (or potentially even valid) if the parameter is provided and the cached plan is stored with NULL (or vice versa). If this is what you ultimately want to achieve, have two different stored procedures and let the app decide which one to call based on whether the parameter is populated or not.

But only do that if you actually observe significant plan/performance differences. As @gbn states, I doubt you will see a huge performance difference in this case, unless the title deed table is huge and unindexed (or not optimally indexed). The typical approach to this is to compare the value OR check that the value is NULL. The multi-procedure approach is much more tedious to maintain for usually little benefit, even with just one optional parameter, and it just compounds with more than one optional parameter.

For very good background reading, check out Erland Sommarskog's article on Dynamic Search Conditions (2008-specific version).