SImple query : use LIKE

Transact-SQL
1

I have a simple query, I use like:

Select * fro t1, t2
Where t1.col1 like '%' + t2.col1 + '%'

The problem is that this query runs on SQL SERVER 2008 R2, but does not work on SQL SERVER 2000 (it does not display any errors but does not terminate)
I waited 30 minutes and then I stopped.

On SL SERVER 2008 R2: this request displays the result in 48 seconds

I tried this:

Select * fro t1, t2
Where t1.col1 like t2.col1 + '%'

It works but does not work until after 2 minutes

I tried this:

Select * fro t1, t2
Where t1.col1 like '%' + t2.col1

The request is blocked

2

I think my request is blocked, why

3

Statistics out of date, or indexes missing?

If there are a lot of rows in T1 and T2 it will take a long time.

You are, of course, doing a JOIN with a cartesian product, so the number of rows for SQL to test is huge. It might well be that the optimiser in SQL2008 does a much smarter job of resolving the query.

4

Get rid of the cross join. Otherwise, not much you can do, as even an index is unlikely to help here.

SELECT * 
FROM t1
INNER JOIN t2 ON t1.col1 LIKE '%' + t2.col1 + '%'