SQL Server 2000 "NO JOIN PREDICATE" warning - Why?

Question

I've got a strange problem with SQL Server 2000, and I just can't think of a reason for this to happen.

There are two tables, both having a combined primary key with a clustered index on it, both keys have the same structure:

(VARCHAR(11), INT, DATETIME)   /* can't change this, so don't suggest I should */

So, joining them like this is easy enough:

SELECT t1.Foo, t2.Bar
FROM   table1 t1 INNER JOIN table2 t2 ON t1.VarcharKey = t2.VarcharKey
WHERE  t1.VarcharKey = 'Foo'

Looking at the query execution plan, I see this:

• Clustered Index Seek [db].[dbo].[table1].[PK_table1] (48%)
• Clustered Index Seek [db].[dbo].[table2].[PK_table2] (51%)
• Nested Loops (Inner Join) (1%) Warning: NO JOIN PREDICATE
• Select (0%)

Now if I do this (note the NVARCHAR string!):

SELECT t1.Foo, t2.Bar
FROM   table1 t1 INNER JOIN table2 t2 ON t1.VarcharKey = t2.VarcharKey
WHERE  t1.VarcharKey = N'Foo'

I get:

• Clustered Index Scan [db].[dbo].[table1].[PK_table1] (98%)
• Clustered Index Seek [db].[dbo].[table2].[PK_table2] (1%)
• Nested Loops (Inner Join) (1%) no warning here
• Select (0%)

This behavior leaves me a bit puzzled.

• Why is there a "NO JOIN PREDICATE" warning, and why does it go away when I change 'Foo' to N'Foo'? My key columns are not of type NVARCHAR, so this should not make any difference, or should it?
• Does the presence of this warning have negative implications or can I ignore it?
• Why does it switch from an Index Seek to an Index Scan?

---

Some background info: Table cardinality is ca. 25,000 records one table, ca. 12,000 records in the other. Database compatibility level is 80 (SQL Server 2000) default collation is SQL_Latin1_General_CP1_CI_AS, if that makes any difference at all.

Here's the contents of @@VERSION:

Microsoft SQL Server 2000 - 8.00.2273 (Intel X86) Mar 7 2008 22:19:58 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

P.S.: I am aware of KB322854, but this is not it, obviously.

Answer 1

From SqlServerCentral:

the queries can have what looks like a perfectly formed join condition. But when you examine the query plan you will see a warning indicating 'No Join Predicate' indicating that 2 of the tables involved do not have a predicate (when joined). Adding a an Option (Force Order) to the query produces a completely different plan and the warning disappears (in some cases). That's how you know that this is the problem. Most of the queries that I have seen that perform better on SQL 2000 are exhibiting this problem. Cumulative update 4 to SP 2 is supposed to solve the problem.

Answer 2

Why does it switch from an Index Seek to an Index Scan?

This is largely a guess, but here goes:

In the first case ('Foo'), MSSQL recognize that the value being search for is a perfect match for the first part of the index on t1, and therefore uses the index to find a record in t1 (Index Seek, possibly a binary search). Having found a record in t1 which has an index which perfectly matches t2, it can use the index to find records in t2.

In the second case, (N'Foo'), MSSQL recognizes that it does NOT have a perfect match between the index and the value being seeked, so it cannot use the index as an index, but must do a full table scan. However, since the index holds the information needed (in a different form) and is smaller than the full table, it can do a full scan of the index as if it were the table (This is faster than scanning the table, since fewer pages need to be read for the disk; mevertheless, it seems to be taking about 90X longer than the index seek)