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

Re: case sensitivity sometimes?

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: case sensitivity sometimes?
Date	Sat, 11 Jun 2011 00:14:08 +0200
Organization	Erland Sommarskog
Lines	45
Message-ID	<Xns9F01265C259DYazorman@127.0.0.1> (permalink)
References	<989c5b50-1c64-4362-bfa6-5c4903c94e9c@m4g2000yqk.googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="31491"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+q1xa0dq0s6E33imYsgxFK"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:dUQfTm3iShPbs0vnvt0EFGoptWE=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:429

Show key headers only | 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