title: SQL Server 2000 "NO JOIN PREDICATE" warning - Why?
body: <p>I've got a strange problem with SQL Server 2000, and I just can't think of a reason for this to happen.</p>
  
  <p>There are two tables, both having a combined primary key with a clustered index on it, both keys have the same structure:</p>
  
  <pre><code>(VARCHAR(11), INT, DATETIME)   /* can't change this, so don't suggest I should */
  </code></pre>
  
  <p>So, joining them like this is easy enough:</p>
  
  <pre><code>SELECT t1.Foo, t2.Bar
  FROM   table1 t1 INNER JOIN table2 t2 ON t1.VarcharKey = t2.VarcharKey
  WHERE  t1.VarcharKey = 'Foo'
  </code></pre>
  
  <p>Looking at the query execution plan, I see this:</p>
  
  <ul>
  <li>Clustered Index Seek [db].[dbo].[table1].[PK_table1] (48%)</li>
  <li>Clustered Index Seek [db].[dbo].[table2].[PK_table2] (51%)</li>
  <li>Nested Loops (Inner Join) (1%) <strong>Warning: NO JOIN PREDICATE</strong> </li>
  <li>Select (0%)</li>
  </ul>
  
  <p>Now if I do this (note the NVARCHAR string!):</p>
  
  <pre><code>SELECT t1.Foo, t2.Bar
  FROM   table1 t1 INNER JOIN table2 t2 ON t1.VarcharKey = t2.VarcharKey
  WHERE  t1.VarcharKey = N'Foo'
  </code></pre>
  
  <p>I get:</p>
  
  <ul>
  <li>Clustered Index Scan [db].[dbo].[table1].[PK_table1] (98%)</li>
  <li>Clustered Index Seek [db].[dbo].[table2].[PK_table2] (1%)</li>
  <li>Nested Loops (Inner Join) (1%) <em>no warning here</em></li>
  <li>Select (0%)</li>
  </ul>
  
  <p>This behavior leaves me a bit puzzled.</p>
  
  <ul>
  <li>Why is there a "NO JOIN PREDICATE" warning, and why does it go away when I change <code>'Foo'</code> to <code>N'Foo'</code>? My key columns are not of type NVARCHAR, so this should not make any difference, or should it?</li>
  <li>Does the presence of this warning have negative implications or can I ignore it?</li>
  <li>Why does it switch from an Index Seek to an Index Scan?</li>
  </ul>
  
  <hr>
  
  <p>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 <code>SQL_Latin1_General_CP1_CI_AS</code>, if that makes any difference at all.</p>
  
  <p>Here's the contents of <code>@@VERSION</code>:</p>
  
  <blockquote>
    <p>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>
  </blockquote>
  
  <p>P.S.: I am aware of <a href="http://support.microsoft.com/kb/322854" rel="nofollow noreferrer">KB322854</a>, but this is not it, obviously.</p>