041321d6   
   From: esquel@sommarskog.se   
      
   bill (billmaclean1@gmail.com) writes:   
   > Reading the plan from the posts was too difficult for me, but I do   
   > have an idea:  Make sure your variable is EXACTLY the same data type   
   > as the column to which you are comparing.   
      
   Very good point.   
      
   > I had a case once where the column was a varchar, and I made the   
   > variable an Nvarchar.  The query performed horribly.  When I changed   
   > the variable to be a varchar, the query sped up by a factor of 4 (or   
   > perhaps it was 10, I can't remember).   
   >   
   > I remember someone telling me that the performance penalty for this   
   > type of datatype mis-match can vary based on the collation page you   
   > are using, but I don't remember the details. I believe that with   
   > certain collations an Nvarchar / varchar mismatch can change the plan   
   > from an index seek to a scan.   
      
   Yes. If you have a Windows collation, SQL Server seeks the index, but   
   in a more roundabout way. This is possible, because the varchar set   
   is a true subset of the full Unicode. My tests indicates that this gives   
   an overhead of factor 2 or 3.   
      
   But if you have an SQL collation, there different rules for some characters   
   between varchar and nvarchar, why the index is dead. How much this costs   
   you in terms of factor, depends on much data there is. It may over a   
   thousand times slower - or worse.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)