Which is an efficient search for last names starting with H?

Which is an efficient search for last names starting with H?

• A. WHERE lastname LIKE '%h'
• B. WHERE lastname = 'h%'
• C. WHERE SUBSTRING(lastname,1,1) = 'h'
• D. WHERE LEFT(lastname,1) = 'H'

Answer: (B)

When you want to find last names that begin with a specific prefix, the goal is to keep the comparison sargable so the database can use an index. The efficient pattern is a simple prefix match using LIKE with the wildcard after the prefix, for example: WHERE lastname LIKE 'H%'. This lets the database quickly locate rows that start with H, especially when an index on lastname exists and the collation is case-insensitive. Avoid wrapping the column in a function (for example, SUBSTRING(lastname, 1, 1) = 'H' or LEFT(lastname, 1) = 'H') because applying a function to the column can prevent the use of the index, leading to slower scans. Similarly, patterns that place the wildcard at the beginning (like '%H%') or that search for the last character instead of the first are not efficient for a prefix search. In short, the most efficient approach is a direct prefix match: lastname LIKE 'H%', which can leverage an index and return results quickly.

When you want to find last names that begin with a specific prefix, the goal is to keep the comparison sargable so the database can use an index. The efficient pattern is a simple prefix match using LIKE with the wildcard after the prefix, for example: WHERE lastname LIKE 'H%'. This lets the database quickly locate rows that start with H, especially when an index on lastname exists and the collation is case-insensitive.

Avoid wrapping the column in a function (for example, SUBSTRING(lastname, 1, 1) = 'H' or LEFT(lastname, 1) = 'H') because applying a function to the column can prevent the use of the index, leading to slower scans. Similarly, patterns that place the wildcard at the beginning (like '%H%') or that search for the last character instead of the first are not efficient for a prefix search.

In short, the most efficient approach is a direct prefix match: lastname LIKE 'H%', which can leverage an index and return results quickly.