Hi,

I have a question about perf of the following query:

declare @detail_level int = 1
, @DETAIL_1 int = 1
, @DETAIL_2 int = 2
, @DETAIL_3 int = 4

select mtbl.*
, case when @detail_level & @DETAIL_1 <> 0 then tbl_1.value else null end
, case when @detail_level & @DETAIL_2 <> 0 then tbl_2.value else null end
, case when @detail_level & @DETAIL_3 <> 0 then tbl_3.value else null end
from mtbl
left outer join tbl_1 on @detail_level & @DETAIL_1 <> 0 and mtbl.key = tbl_1.key
left outer join tbl_2 on @detail_level & @DETAIL_2 <> 0 and mtbl.key = tbl_2.key
left outer join tbl_3 on @detail_level & @DETAIL_3 <> 0 and mtbl.key = tbl_3.key
where mtbl.key = @something

Will the query engine optimize the query by using the detail level filter to avoid unnecessary table join and unnecessary table row access for those detail table columns?

If yes, will query engine even not acquire index locks for those unnecessary table?

I captured the query plan in SQL Server 2008 and index search operations still existed even detail level did not match. But the query with less details did (~ 30%) faster than the one with full details (data volumn ~ 500,000).

Thanks.