Skip to main content
added 228 characters in body
Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

In this case you could also consider

SELECT * FROM 
TableA
WHERE acccode LIKE '1[3-6]1%'
      OR acccode LIKE '121-111%'

As a simplification though the fully expanded version might be more efficient.

If you have no useful index you might prefer just using

SELECT * FROM 
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161') 
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

If you have no useful index you might prefer just using

SELECT * FROM 
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161') 
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

In this case you could also consider

SELECT * FROM 
TableA
WHERE acccode LIKE '1[3-6]1%'
      OR acccode LIKE '121-111%'

As a simplification though the fully expanded version might be more efficient.

If you have no useful index you might prefer just using

SELECT * FROM 
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161') 
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.

Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

If you have no useful index you might prefer just using

SELECT * FROM 
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161') 
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.