SUBSTRING vs LEFT in SQL SERVER

Question

I posted a question here, but no one answer so I tried to focus what's making my query slow and a question came into my mind. Which one is faster and more efficient? LEFT or SUBSTRING?

Answer 1

There is no difference at all between left and substring because left is translated to substring in the execution plan.

For example:

select substring(col, 1, 2),
       left(col, 3)
from YourTable

will look like this in the execution plan

<DefinedValue>
  <ColumnReference Column="Expr1004" />
  <ScalarOperator ScalarString="substring([col],(1),(2))">
    <Intrinsic FunctionName="substring">
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="col" />
        </Identifier>
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(1)" />
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(2)" />
      </ScalarOperator>
    </Intrinsic>
  </ScalarOperator>
</DefinedValue>
<DefinedValue>
  <ColumnReference Column="Expr1005" />
  <ScalarOperator ScalarString="substring([col],(1),(3))">
    <Intrinsic FunctionName="substring">
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="col" />
        </Identifier>
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(1)" />
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(3)" />
      </ScalarOperator>
    </Intrinsic>
  </ScalarOperator>
</DefinedValue>

Answer 2

SQL Server is a database. You dod not ask questions of which string processing function is 'faster'. You ask the questions 'which can use an index?' and 'do I have the required index?'. Is all about data access, because disks are sloooooow, not about shifting CPU registers.

So, Which can use an index? (which one is sargable?). In theory LEFT could use an index, but in practice it usually does not. SUBSTRING cannot. Instead of SUBSTRING use Full Text.

Design your data model to take advantage of sargable expressions, index accordingly. That's all there is to it, there is no magic bullet. Avoid scans.