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

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

Re: case sensitivity sometimes?

Show all headers | View raw

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

Hmm, yes, if it was not for the code itself is a bit inconsistent when
it comes to
casing. And recently, we switched gui, using an internal .net
framework
used within other parts of the company. And that framework
uses the case of TABLE_NAME.column_name. Which the background programs
does not.
And the co-worker use sql*plus for Oracle all the time,
and now I suspect sqlcmd will be popular.
(which inspires me to create another thread about describe tables)

Also, some parts of the system changes a lot, some parts are very
static.
Given that the old parts stems from the late 80'/early 90's,
the way of coding has changed.
The old code uses VARIABLE_NAMES is capital case,
while the new stuff uses Variable_Names in Camel_Case instead.
This sometimes also applies to the sql-statements in the code as well.

Another reason is that the language all is written in (but for the
gui)
is Ada, which is case-ignorant, both for reserved words, and for
variables.
(Ada83, then Ada95, and now Ada05)


> Why that collation? Why not Finnish_Swedish_CS_AS, which would be the
> natural choice?

Giving my name, yes that would be natural. But the application runs
in Denmark/Norway/Finland/The UK/BeNeLux and Germany as well.
I'd like to keep the same collation everywhere.
Order by clauses on strings having language-specific characters
are very rare, if they ever occur.
Case equivalence is something else.

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

The reason is simply ignorance. I did not have a clue, found the one I
use,
and moved on. This concept is a bit new to me, even if I see the point
of
having the different collations.
I'll switch to Latin1_General_CS_AS.
Thanks for bringing it up.


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

Perfect. I'll test the coming week.
The index thing is a non-existing problem,
the table only holds max 10-15 records or so,
And its only run at at system startup.
But the tip may come in handy in other situations.

Again, thanks Erland
--
Björn

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