This article was previously published under Q225093
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55032 (SQLBUG_70)
An inner join between two tables with multiple LIKE clauses produces a poor query plan if the LIKE clauses use variables instead of string literals.
To work around this use one of the following:
Replace the variables with string constants.
Provide a hint to force a loop join. For example, use an INNER LOOP JOIN instead of an INNER JOIN.
Use the system stored procedure sp_executesql to execute the query, passing the variables as parameters. For syntax details see the Books Online article "sp_executesql (T-SQL)".
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
The following conditions must be met to encounter this bug:
The query must contain an inner join.
The WHERE clause of the query must contain multiple LIKE predicates.
The LIKE predicates must be passed a variable, not a string constant (that is, "... AND column LIKE @myvar1 ...").
The inner join may use either ANSI-standard or Transact-SQL (TSQL) join syntax.
For example, the following query demonstrates this problem:
SELECT p.first_nameFROM person pINNER JOIN customer c ON p.person_id = c.person_id WHERE p.last_name LIKE @last_name AND p.first_name LIKE @first_name