Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #429

Re: case sensitivity sometimes?

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

case sensitivity sometimes? björn lundin <b.f.lundin@gmail.com> - 2011-06-10 02:51 -0700
  Re: case sensitivity sometimes? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-11 00:14 +0200
    Re: case sensitivity sometimes? björn lundin <b.f.lundin@gmail.com> - 2011-06-12 07:15 -0700
      Re: case sensitivity sometimes? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-12 20:40 +0200
        Re: case sensitivity sometimes? björn lundin <b.f.lundin@gmail.com> - 2011-06-12 13:10 -0700
          Re: case sensitivity sometimes? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-13 00:05 +0200

csiph-web