Re: SQL2005 collation vs Oracle

As for data Oracle has it completely wrong,

If you do not like the default collation, then change it. Both SQL Server and Oracle let you change the case sensitivity of its data.

collation should reflect the real world if a salesman shouts out AKA do they mean aka, Aka, AKa, AKA or what????

Agreed with your example, but in the real world, case can matter for some data. Is your name "tony", "Tony", or "ToNy"? It does make a difference when referring to proper nouns. If it didn't make a difference, then "e. e. cummings" name would not have garnered so much attention throughout the years.

Sometimes, case does not matter, sometimes it does.

You should not have to rely on doing this data verification yourself, adding constraints to check case etc... is just making up for a poor initial decision to go with case sensitivity.

This is the biggest pain in the arse in the business intelligence and reporting area.

Agreed! I worked on a project where we had to compare the address someone entered with what was in our database. Using case sensitive searching, "ave" does not match "Ave" or "AVE". Of course, it does not match "Avenue" either but that is a different animal to skin.

Case Insensitive is how the real world works and that should be the default for any database 'period'.

I would disagree. Modelling the real world is rarely as simple as black and white. If one comes up with a hard and fast rule, you'll come up with an exception to that rule. If you do not like my example above with proper nouns, then consider this example:

I have a business rule to store my user's password in a table in my database. That password is composed of characters and numbers. Furthermore, to increase my password complexity, I require at least one lower case character and at least one upper case character. Case sensitivity is now a must. Windows uses case sensitivity in its passwords as do some applications.

In many cases, the real world does not matter what case you use. But in some cases, case does matter.

As for Object names it shouldn't matter but case insenitive promotes poor programming practice.

It respectfully disagree here as well. If object names are allowed to be case sensitive, then a table with name "Employees" would differ from a table named "employees". Two tables with two different names (based on case), but seem to be representing the same real world entity. Which table do I use to to get my company's employee information? Object names being case sensitive can lead to confusion when the only difference between the objects (on the surface) is the case sensitivity to their names.

Consider portability to other products and systems.....

So assume that you allow object names to be case sensitive. Then assume that you port from a RDBMS that allows this to an RDBMS that does not. You will run in to a problem trying to create that second Employees table no matter how it is spelled (case-wise). If you truly want to consider portability, you will make your object names different regardless of case.

And object names being case insensitive or case sensitive really is not about programming, but rather database schema design. Maybe it's splitting hairs, but these are two different things.

Cheers!
Brian


--
===================================================================

Brian Peasland
oracle_dba@xxxxxxxxxxxxxxxxxxx
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
.

Relevant Pages

  • Re: SQL2005 collation vs Oracle
    ... the time does it matter 80/10??? ... will run in to a problem trying to create that second Employees table no ... and Oracle let you change the case sensitivity of its data. ... entered with what was in our database. ...
    (comp.databases.ms-sqlserver)
  • Re: Grrrrrrrr
    ... reason, to use a term so loaded now if you don't have to. ... in prison and elsewhere) it's a matter of such ... sensitivity, and it's a fact that has been so denigrated by society, ... The historical uses of the word, which are legion, are ...
    (rec.music.opera)
  • Re: Newbie help on formatting and alignment
    ... sensitivity on the matter. ... Actually, it's a variant from the Deitel, ...
    (comp.lang.java.help)
  • Re: More oddness from the sky pixie brigade
    ... "Wicked Uncle Nigel" wrote in message ... This is a matter that needs to be handled with sensitivity. ... "Can you point to the exact passage in the Torah where god forbids activating motion sensitive electric lights on the Sabbath? ...
    (uk.rec.motorcycles)
  • Re: I need some help!
    ... which of course make their "ICs" employees (whether they want to ... deference to the sensitivity of the MT? ...
    (sci.med.transcription)