8b4357b6   
   From: esquel@sommarskog.se   
      
   björn lundin (b.f.lundin@gmail.com) writes:   
   > However, since my coworkers would become upset if the   
   > db differed on cAsE of entity names, I set the db to   
   > be insensitive to casing, when it comes to   
   > table names and column names.   
      
   Personally, I have never seen the point with calling a table Orders in one   
   minute, orders in the next and then ORDERS on the day after. I would smack   
   on a case-insenstive collation and go with that.   
      
   > and still get the same result-set. This is not OK in the application.   
   > So, I append  COLLATE SQL_Latin1_General_CP1_CS_AS  on all string   
   > fields in my tables like:   
      
   Why that collation? Why not Finnish_Swedish_CS_AS, which would be the   
   natural choice? Or at least Latin1_General_CS_AS? Avoid the SQL collations,   
   there are some nasty traps and funny thing with these. They exist for   
   legacy only.   
      
   > But I now get this   
   >   
   > Msg 468, Level 16, State 9, Server SELNDRDW03ARON, Line 18   
   > Cannot resolve the collation conflict between   
   > "SQL_Latin1_General_CP1_CS_AS"   
   > and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.   
   >   
   > And I see the reason. The view's string fields are not case sensitive,   
   > but BSYSNO's string fields are.   
      
   You need to use the COLLATE clause to force the collation on either   
   side. Which probably should be the INFORMATION_SCHEMA side, since else   
   you will kill the index on the column in BSYSNO:   
      
     where not exists (select 'x' from INFORMATION_SCHEMA.TABLES where   
     TABLE_NAME COLLATE Finnish_Swedish_CS_AS =BSYSEQU)   
      
   I removed the rtrim since it's not needed, and that's another index killer.   
      
   --   
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)