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 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: 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 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