title: What is query execution doing to this query in SQL Server 2005?
body: <p>Today I found this query in our code that pulls up a list of errors from our database:</p>
  
  <pre><code>SELECT *
  FROM   (
          SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE, *
          FROM   core.LOG
          WHERE  logtypeid = 1
                 AND title LIKE 'Error: (%'
                 AND lastmodified &gt; '2010-06-21T00:00:00'
                 AND lastmodified &lt; '2010-06-22T00:00:00'
          ) serviceerrors
  WHERE SERVICE = 'CheckHelpDeskEmail'  
  </code></pre>
  
  <p>It fails with the following error: </p>
  
  <blockquote>
    <p>Invalid length parameter passed to the
    SUBSTRING function.</p>
  </blockquote>
  
  <p>If I remove the <code>WHERE</code> clause on the very last line it works fine. Alternatively, if I take the <code>WHERE</code> clause from the inner subquery and move it to the main query it works fine. So this works:</p>
  
  <pre><code>SELECT *
  FROM   (
          SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE, *
          FROM   core.LOG
          ) serviceerrors
  WHERE  logtypeid = 1
  AND title LIKE 'Error: (%'
  AND lastmodified &gt; '2010-06-21T00:00:00'
  AND lastmodified &lt; '2010-06-22T00:00:00'
  AND SERVICE = 'CheckHelpDeskEmail' 
  </code></pre>
  
  <p>Does anyone know why? I think the problem is that SQL is actually making multiple runs through the core.LOG table and the first time it simply runs the Substring line on the whole table which fails since some lines will not have a ')' in them. However, if it runs the remaining lines in the <code>WHERE</code> clause it won't find any rows missing a ')'. Then it makes another run through the core.LOG table and runs the remaining filters on it. This seems unefficient since it will end up running the Substring function on millions of rows, vs about 15 rows from the remaining filters in the <code>WHERE</code> clause.</p>
  
  <p>Here's my XML Execution plan</p>
  
  <pre><code>    &lt;?xml version="1.0" encoding="utf-16"?&gt;
  &lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.3310.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;
    &lt;BatchSequence&gt;
      &lt;Batch&gt;
        &lt;Statements&gt;
          &lt;StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="36.4574" StatementText="SELECT *&amp;#xD;&amp;#xA;FROM   (&amp;#xD;&amp;#xA;       SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE,&amp;#xD;&amp;#xA;               *&amp;#xD;&amp;#xA;        FROM   core.LOG&amp;#xD;&amp;#xA;        WHERE  logtypeid = 1&amp;#xD;&amp;#xA;               AND title LIKE 'Error: (%'&amp;#xD;&amp;#xA;               AND lastmodified &amp;gt; '2010-06-21T00:00:00'&amp;#xD;&amp;#xA;               AND lastmodified &amp;lt; '2010-06-22T00:00:00'&amp;#xD;&amp;#xA;        ) serviceerrors&amp;#xD;&amp;#xA;WHERE SERVICE = 'CheckHelpDeskEmail'  &amp;#xD;&amp;#xA;" StatementType="SELECT"&gt;
            &lt;StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /&gt;
            &lt;QueryPlan CachedPlanSize="47" CompileTime="10" CompileCPU="7" CompileMemory="544"&gt;
              &lt;RelOp AvgRowSize="4544" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="36.4574"&gt;
                &lt;OutputList&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" /&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" /&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" /&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                  &lt;ColumnReference Column="Expr1003" /&gt;
                &lt;/OutputList&gt;
                &lt;ComputeScalar&gt;
                  &lt;DefinedValues&gt;
                    &lt;DefinedValue&gt;
                      &lt;ColumnReference Column="Expr1003" /&gt;
                      &lt;ScalarOperator ScalarString="substring([Aqueduct].[Core].[Log].[Title],(9),patindex(N'%)%',[Aqueduct].[Core].[Log].[Title])-(9))"&gt;
                        &lt;Intrinsic FunctionName="substring"&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Identifier&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                            &lt;/Identifier&gt;
                          &lt;/ScalarOperator&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Const ConstValue="(9)" /&gt;
                          &lt;/ScalarOperator&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Arithmetic Operation="SUB"&gt;
                              &lt;ScalarOperator&gt;
                                &lt;Intrinsic FunctionName="patindex"&gt;
                                  &lt;ScalarOperator&gt;
                                    &lt;Const ConstValue="N'%)%'" /&gt;
                                  &lt;/ScalarOperator&gt;
                                  &lt;ScalarOperator&gt;
                                    &lt;Identifier&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                                    &lt;/Identifier&gt;
                                  &lt;/ScalarOperator&gt;
                                &lt;/Intrinsic&gt;
                              &lt;/ScalarOperator&gt;
                              &lt;ScalarOperator&gt;
                                &lt;Const ConstValue="(9)" /&gt;
                              &lt;/ScalarOperator&gt;
                            &lt;/Arithmetic&gt;
                          &lt;/ScalarOperator&gt;
                        &lt;/Intrinsic&gt;
                      &lt;/ScalarOperator&gt;
                    &lt;/DefinedValue&gt;
                  &lt;/DefinedValues&gt;
                  &lt;RelOp AvgRowSize="4342" EstimateCPU="0.0288036" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="36.4574"&gt;
                    &lt;OutputList&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" /&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" /&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" /&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                    &lt;/OutputList&gt;
                    &lt;Parallelism&gt;
                      &lt;RelOp AvgRowSize="4342" EstimateCPU="0.0128582" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="2" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="36.4286"&gt;
                        &lt;OutputList&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" /&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" /&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" /&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                        &lt;/OutputList&gt;
                        &lt;Filter StartupExpression="false"&gt;
                          &lt;RelOp AvgRowSize="4342" EstimateCPU="0.0235734" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11279.1" LogicalOp="Inner Join" NodeId="3" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="36.4157"&gt;
                            &lt;OutputList&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" /&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" /&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" /&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                            &lt;/OutputList&gt;
                            &lt;NestedLoops Optimized="true" WithUnorderedPrefetch="true"&gt;
                              &lt;OuterReferences&gt;
                                &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                                &lt;ColumnReference Column="Expr1004" /&gt;
                              &lt;/OuterReferences&gt;
                              &lt;RelOp AvgRowSize="19" EstimateCPU="0.00628203" EstimateIO="0.0186806" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11279.1" LogicalOp="Index Seek" NodeId="6" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0249626"&gt;
                                &lt;OutputList&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                                &lt;/OutputList&gt;
                                &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;
                                  &lt;DefinedValues&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                                    &lt;/DefinedValue&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                                    &lt;/DefinedValue&gt;
                                  &lt;/DefinedValues&gt;
                                  &lt;Object Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Index="[IX_LastModified]" /&gt;
                                  &lt;SeekPredicates&gt;
                                    &lt;SeekPredicate&gt;
                                      &lt;StartRange ScanType="GT"&gt;
                                        &lt;RangeColumns&gt;
                                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                                        &lt;/RangeColumns&gt;
                                        &lt;RangeExpressions&gt;
                                          &lt;ScalarOperator ScalarString="'2010-06-21 00:00:00.000'"&gt;
                                            &lt;Const ConstValue="'2010-06-21 00:00:00.000'" /&gt;
                                          &lt;/ScalarOperator&gt;
                                        &lt;/RangeExpressions&gt;
                                      &lt;/StartRange&gt;
                                      &lt;EndRange ScanType="LT"&gt;
                                        &lt;RangeColumns&gt;
                                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" /&gt;
                                        &lt;/RangeColumns&gt;
                                        &lt;RangeExpressions&gt;
                                          &lt;ScalarOperator ScalarString="'2010-06-22 00:00:00.000'"&gt;
                                            &lt;Const ConstValue="'2010-06-22 00:00:00.000'" /&gt;
                                          &lt;/ScalarOperator&gt;
                                        &lt;/RangeExpressions&gt;
                                      &lt;/EndRange&gt;
                                    &lt;/SeekPredicate&gt;
                                  &lt;/SeekPredicates&gt;
                                &lt;/IndexScan&gt;
                              &lt;/RelOp&gt;
                              &lt;RelOp AvgRowSize="4447" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="11278.1" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="36.3672"&gt;
                                &lt;OutputList&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" /&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" /&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" /&gt;
                                  &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                                &lt;/OutputList&gt;
                                &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;
                                  &lt;DefinedValues&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                                    &lt;/DefinedValue&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" /&gt;
                                    &lt;/DefinedValue&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" /&gt;
                                    &lt;/DefinedValue&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" /&gt;
                                    &lt;/DefinedValue&gt;
                                    &lt;DefinedValue&gt;
                                      &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                                    &lt;/DefinedValue&gt;
                                  &lt;/DefinedValues&gt;
                                  &lt;Object Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Index="[PK_Core_Log]" TableReferenceId="-1" /&gt;
                                  &lt;SeekPredicates&gt;
                                    &lt;SeekPredicate&gt;
                                      &lt;Prefix ScanType="EQ"&gt;
                                        &lt;RangeColumns&gt;
                                          &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                                        &lt;/RangeColumns&gt;
                                        &lt;RangeExpressions&gt;
                                          &lt;ScalarOperator ScalarString="[Aqueduct].[Core].[Log].[LogId]"&gt;
                                            &lt;Identifier&gt;
                                              &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" /&gt;
                                            &lt;/Identifier&gt;
                                          &lt;/ScalarOperator&gt;
                                        &lt;/RangeExpressions&gt;
                                      &lt;/Prefix&gt;
                                    &lt;/SeekPredicate&gt;
                                  &lt;/SeekPredicates&gt;
                                &lt;/IndexScan&gt;
                              &lt;/RelOp&gt;
                            &lt;/NestedLoops&gt;
                          &lt;/RelOp&gt;
                          &lt;Predicate&gt;
                            &lt;ScalarOperator ScalarString="substring([Aqueduct].[Core].[Log].[Title],(9),patindex(N'%)%',[Aqueduct].[Core].[Log].[Title])-(9))=N'CheckHelpDeskEmail' AND [Aqueduct].[Core].[Log].[Title] like N'Error: (%' AND [Aqueduct].[Core].[Log].[LogTypeId]=(1)"&gt;
                              &lt;Logical Operation="AND"&gt;
                                &lt;ScalarOperator&gt;
                                  &lt;Compare CompareOp="EQ"&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Intrinsic FunctionName="substring"&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Identifier&gt;
                                            &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                                          &lt;/Identifier&gt;
                                        &lt;/ScalarOperator&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Const ConstValue="(9)" /&gt;
                                        &lt;/ScalarOperator&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Arithmetic Operation="SUB"&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Intrinsic FunctionName="patindex"&gt;
                                                &lt;ScalarOperator&gt;
                                                  &lt;Const ConstValue="N'%)%'" /&gt;
                                                &lt;/ScalarOperator&gt;
                                                &lt;ScalarOperator&gt;
                                                  &lt;Identifier&gt;
                                                    &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                                                  &lt;/Identifier&gt;
                                                &lt;/ScalarOperator&gt;
                                              &lt;/Intrinsic&gt;
                                            &lt;/ScalarOperator&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Const ConstValue="(9)" /&gt;
                                            &lt;/ScalarOperator&gt;
                                          &lt;/Arithmetic&gt;
                                        &lt;/ScalarOperator&gt;
                                      &lt;/Intrinsic&gt;
                                    &lt;/ScalarOperator&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Const ConstValue="N'CheckHelpDeskEmail'" /&gt;
                                    &lt;/ScalarOperator&gt;
                                  &lt;/Compare&gt;
                                &lt;/ScalarOperator&gt;
                                &lt;ScalarOperator&gt;
                                  &lt;Intrinsic FunctionName="like"&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Identifier&gt;
                                        &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" /&gt;
                                      &lt;/Identifier&gt;
                                    &lt;/ScalarOperator&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Const ConstValue="N'Error: (%'" /&gt;
                                    &lt;/ScalarOperator&gt;
                                  &lt;/Intrinsic&gt;
                                &lt;/ScalarOperator&gt;
                                &lt;ScalarOperator&gt;
                                  &lt;Compare CompareOp="EQ"&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Identifier&gt;
                                        &lt;ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" /&gt;
                                      &lt;/Identifier&gt;
                                    &lt;/ScalarOperator&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Const ConstValue="(1)" /&gt;
                                    &lt;/ScalarOperator&gt;
                                  &lt;/Compare&gt;
                                &lt;/ScalarOperator&gt;
                              &lt;/Logical&gt;
                            &lt;/ScalarOperator&gt;
                          &lt;/Predicate&gt;
                        &lt;/Filter&gt;
                      &lt;/RelOp&gt;
                    &lt;/Parallelism&gt;
                  &lt;/RelOp&gt;
                &lt;/ComputeScalar&gt;
              &lt;/RelOp&gt;
            &lt;/QueryPlan&gt;
          &lt;/StmtSimple&gt;
        &lt;/Statements&gt;
      &lt;/Batch&gt;
    &lt;/BatchSequence&gt;
  &lt;/ShowPlanXML&gt;
  </code></pre>