title: Does SQL Server 2012 Lazy Join
body: <p>I am working on an application that allows users to dynamically choose what they see. The list of columns and tables those belong to is huge. I'm working on building a dynamic query but I don't want to have to store all the join logic if I don't have too.
  An example is I have a query that has LEFT JOINS for 10 tables but I only select and filter by one table. Is SQL Server 2012 Smart enough to not join on the other tables that are not needed?</p>
  
  <p>I know Oracle's DBMS works like this but my company uses SQL Server 2012 so I'm stuck with that.</p>
  
  <p>Example Query where the last 4 LEFT JOINS don't need to be performed:</p>
  
  <pre><code>SELECT [IncidentIncidentNumberModValue].[IncidentNumber]
      ,IncidentNumberModifierNotValue.[Value] AS IncidentNumberNotValue
      ,[Incident].[IncidentDate]
      ,[Incident].[ResponseNumber]
      ,[Incident].[PatientCareReportNumber]
      ,[IncidentType].[Value] AS IncidentType
      ,[SceneStreetAddressModValue].[StreetAddress2] AS IncidentAddress
      ,IncidentStreetAddressNotValue.[Value] AS IncidentAddressNotValue
      ,COUNT(*) OVER() AS TotalRecordCount
  FROM [EmsEvent].[Incident]
      LEFT JOIN [Resource].[IncidentType] ON [EmsEvent].[Incident].[IncidentType] = [Resource].[IncidentType].[IncidentTypeID]
      LEFT JOIN [EmsEvent].[IncidentIncidentNumberModValue] ON [EmsEvent].[Incident].[IncidentID] = [EmsEvent].[IncidentIncidentNumberModValue].[IncidentID]
      LEFT JOIN [GlobalResource].[IncidentNumberModifier] ON [EmsEvent].[IncidentIncidentNumberModValue].[NotValue] = [GlobalResource].[IncidentNumberModifier].[DataElementID]
      LEFT JOIN [GlobalResource].[NotValue] IncidentNumberModifierNotValue ON [GlobalResource].[IncidentNumberModifier].[NotValueID] = IncidentNumberModifierNotValue.[NotValueID]
      LEFT JOIN [EmsEvent].[Scene] ON [EmsEvent].[Scene].[IncidentID] = [EmsEvent].[Incident].[IncidentID]
      LEFT JOIN [EmsEvent].[SceneStreetAddressModValue] ON [EmsEvent].[Scene].[SceneID] = [EmsEvent].[SceneStreetAddressModValue].[SceneID]
      LEFT JOIN [GlobalResource].[IncidentStreetAddressModifier] ON [EmsEvent].[SceneStreetAddressModValue].[NotValue] = [GlobalResource].[IncidentStreetAddressModifier].[DataElementID]
      LEFT JOIN [GlobalResource].[NotValue] IncidentStreetAddressNotValue ON [GlobalResource].[IncidentStreetAddressModifier].[NotValueID] = IncidentStreetAddressNotValue.[NotValueID]
      LEFT JOIN [EmsEvent].[CustomConfig] on [EmsEvent].[Incident].[IncidentID] = [EmsEvent].[CustomConfig].[IncidentID]
      LEFT JOIN [EmsEvent].[IncidentDisasterType] on [EmsEvent].[Incident].[IncidentID] = [EmsEvent].[IncidentDisasterType].[IncidentID]
      LEFT JOIN [EmsEvent].[Response] on [EmsEvent].[Scene].[SceneID] = [EmsEvent].[Response].[SceneID]
      LEFT JOIN [EmsEvent].[CrewMember] on [EmsEvent].[Response].[ResponseID] = [EmsEvent].[CrewMember].[ResponseInformationID]
  
  WHERE [EmsEvent].[Incident].[DeletedStatus] = 0
      AND [EmsEvent].[Incident].AgencyID = '607CEE05-276D-49A1-B6BF-FD606EFC2377'
  ORDER BY [EmsEvent].[IncidentIncidentNumberModValue].[IncidentNumber] ASC
  OFFSET 0 ROWS
  FETCH NEXT 25 ROWS ONLY
  OPTION(recompile)
  </code></pre>