Why do SUBSTRING or LEFT make the query much slower?

Question

I have a blacklist of people you can’t contact. When I want to see if a person is on this list, I do the following:

-- Query 1
SELECT * 
FROM bldb.dbo.blacklist l
WHERE l.matchcode
    = dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert')

The query is very fast, since there is an index in the matchcode column, and it fn_matchcodeis deterministic.

Think of a coincidence as a concise form of address and name, which helps me not to be mistyped in street names, etc. It consists of 22 characters: 13 for the address, 9 for the name. When I want to see if any of the 1 Sesame Street 12345 is blacklisted, I do the following:

-- Query 2
SELECT * 
FROM bldb.dbo.blacklist l
WHERE LEFT(l.matchcode,13)
    = LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13)

It lasts a very long time ...

On the contrary, it runs much faster:

-- Query 3
SELECT * 
FROM bldb.dbo.blacklist l
WHERE LEFT(l.matchcode,13)
    = (SELECT LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13))

, where ! ? UDF . LEFT(), ?

EDIT:

, , . , .

:

-- Query 4
SELECT * 
FROM bldb.dbo.blacklist
WHERE matchcode LIKE LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13) + '%'

- . , fn_matchcode .

fn_matchcode :

-- Query 5
SELECT * 
FROM bldb.dbo.blacklist
WHERE matchcode LIKE '12345SSMSTRT1%'

! ?

Answer 1

№4 №5 , , - ? , , , . , , , .

, ?

SELECT * 
FROM bldb.dbo.blacklist WITH (FORCESEEK)
WHERE matchcode LIKE 
  LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13) + '%'

Answer 2

SELECT * 
FROM bldb.dbo.blacklist l
WHERE l.matchcode LIKE 
  LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13) + '%'

. , .

Answer 3

- ​​, left where, , .

Answer 4

, where !

, - .

, (- SELECT), WHERE .

, VARCHAR (, 10 VARCHAR (150)) - . INT - 4 , ...

Answer 5

For the request to be executed quickly, there must be a pointer to the requested thing. If you intend to query based on a value calculated from one or more columns, you need to have an indexed column containing the result of this calculation.