SQL Server 2000 "NO JOIN PREDICATE" Warning - Why?
I'm having a weird problem with SQL Server 2000 and I just can't think of why this would happen.
There are two tables, each with a combined primary key with a clustered index, both keys have the same structure:
(VARCHAR(11), INT, DATETIME) /* can't change this, so don't suggest I should */
So, attaching them is so easy:
SELECT t1.Foo, t2.Bar
FROM table1 t1 INNER JOIN table2 t2 ON t1.VarcharKey = t2.VarcharKey
WHERE t1.VarcharKey = 'Foo'
Looking at the execution plan for the query, I see the following:
- Clustered Index Seek [db]. [dbo]. [table1]. [PK_table1] (48%)
- Find the clustered index [db]. [dbo]. [table2]. [PK_table2] (51%)
- Nested Loops (Internal Registration) (1%) Warning: NO JOIN FOR CHECK
- Select (0%)
Now if I do this (notice the NVARCHAR line!):
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 (internal registration) (1%) no warnings here
- Select (0%)
This behavior leaves me puzzled.
- Why is there a "NO JOIN PREDICATE" warning and why does it disappear when I change
'Foo'
toN'Foo'
? My key columns are not of type NVARCHAR, so it shouldn't make any difference or should it? - Does this warning have negative consequences or can I ignore it?
- Why does it switch from index lookup to index scan?
Some background information: the table capacity is approx. 25,000 entries per table, approx. 12,000 entries to another. The database compatibility level is 80 (SQL Server 2000) by default SQL_Latin1_General_CP1_CI_AS
, if it matters at all.
Here's the content @@VERSION
:
Microsoft SQL Server 2000 - 8.00.2273 (Intel X86) March 7, 2008 10:19:58 PM Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
PS: I know KB322854 , but it's not, obviously.
source to share
Why does it switch from index lookup to index scan?
This is pretty much a guess, but it says here:
In the first case ('Foo'), MSSQL recognizes that the value being the search is a perfect match for the first part of the index at t1 and therefore uses the index to find the entry at t1 (Index Seek, possibly a binary search). Once he finds a record in t1 that has an index that matches t2 perfectly, he 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 requested value, so it cannot use the index as an index, but must do a full scan table. However, since the index contains the information it needs (in a different form) and is smaller than the full table, it can perform a full index scan as if it were a table (which is faster than a table scan, since fewer pages need to be read for disk, however, it seems to take about 90 times longer than index lookup)
source to share
requests can have what looks like a well-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 joining). Adding the (Force Order) option to the request results in a completely different plan and the warning disappears (in some cases). As you know this is the problem. Most of the queries I've seen that work better on SQL 2000 demonstrate this problem. Cumulative Update 4 to SP 2 should fix the issue.
source to share