SQL2005 collation vs Oracle

[Nasir]

Hi there,

Is it posible to set the collation of SQL server 2005 like Oracle, which is
that objects names and column names are case-insensitive, but data is
sensitive;e.g:

In Oracle:
--create lower-case table and column name
create table t1(c1 char(10));
--Mike with upper-case J
insert into t1 values('Mike');
--follwoing I'm changing case in the table name, but it works, however
'Mike' has to be with uppewr case M
select * from T1 where c1='Mike'

Above test fails in SQL server. Is there a collation which can make data
'literals' sensitive, but data dictionary or the object names and column
names case insensitive?

TIA,
Nasir

[Tony Rogerson]

>I think Oracle got it right. It should not matter if you use MYTABLE,

MyTable, or mytable in your SQL statement FROM clause. The database
should recognize the table name. But data should be stored exactly as
entered so that mike, Mike, and MIKE should be different. There are
As for data Oracle has it completely wrong, collation should reflect the
real world if a salesman shouts out AKA do they mean aka, Aka, AKa, AKA or
what???? 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.

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

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

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

IMHO....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mark D Powell" <Ma*********@ed s.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .I think Oracle got it right. It should not matter if you use MYTABLE,
MyTable, or mytable in your SQL statement FROM clause. The database
should recognize the table name. But data should be stored exactly as
entered so that mike, Mike, and MIKE should be different. There are
easy ways to handle forcing the data into a standard storage format via
column level constraints, table triggers, and single row functions like
UPPER, LOWER, and INITCAP. And there are ways to perform case
insensative compares where needed or desired.

Standarding data entry so that you do not end up with what amounts to
duplicates due to variation of entry has been an application issue
since computers were invented. Example: Johnson Inc. vs Johnson
Incorporated. Allowing the CAPLOCK key to add additional variables to
the mix is just asking for data integrity problems. Ever column of
every table in the application should have data entry rules. Then you
do not end up having to know or figure out if you are looking for MIKE,
mike, or Mike.

IMHO -- Mark D Powell --

[Alexander Kuznetsov]

Latin1_General_ CS_AS will solve some problems, but not all:

SQL Server:

CREATE TABLE t1(c1 CHAR(10) COLLATE Latin1_General_ CS_AS NOT NULL);
INSERT INTO t1 VALUES('AAA');
INSERT INTO t1 VALUES('A_A');

SELECT * FROM T1 order by c1;

c1
----------
A_A
AAA

(2 row(s) affected)

drop table t1;

The same script in Oracle running on UNIX returns rows in a different
order:

C1
----------
AAA
A_A
2 rows selected

[Brian Peasland]

> 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
or********@nosp am.peasland.net
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

[Tony Rogerson]

> 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.
The default setting should reflect what is the general, and in my experience
and opinion case insensitive is the general, all you are doing is comparing
and building a collation rule based on grammar.

Think through the example, there is no case in speech only gramatical rules.
Is Mac Mc, Mac etc...

Case does matter at times, but is it the general case, what proportion of
the time does it matter 80/10 (not/matters)???
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.

Assume you develop on your oracle system which is case insensitive, the
sloppy developer references the table as Employee and then employee in views
meaning the same table - it will work, now move that implementation to a
case sensitive database and it breaks - thats my point, case insensitive
promotes sloppy programming, I follow Erlands example, all object names and
identifiers are lower case and then you have no problems.

The problems gets worse when programmers start referencing those identifier
names in applications... ..

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Brian Peasland" <or********@nos pam.peasland.ne t> wrote in message
news:Iz******** @igsrsparc2.er. usgs.gov...

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
or********@nosp am.peasland.net
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

[DA Morgan]

Alexander Kuznetsov wrote:

Latin1_General_ CS_AS will solve some problems, but not all:

SQL Server:

CREATE TABLE t1(c1 CHAR(10) COLLATE Latin1_General_ CS_AS NOT NULL);
INSERT INTO t1 VALUES('AAA');
INSERT INTO t1 VALUES('A_A');

SELECT * FROM T1 order by c1;

c1
----------
A_A
AAA

(2 row(s) affected)

drop table t1;

The same script in Oracle running on UNIX returns rows in a different
order:

C1
----------
AAA
A_A
2 rows selected

Default tables in Oracle, by definition, are heap tables. Expecting
a specific return ordering of rows, unless you explicitly specify
ORDER BY makes no sense.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Alexander Kuznetsov]

Daniel,

you failed to notice a loud and clear ORDER BY clause in my script

SELECT * FROM T1 order by c1;

and decided I'm a newbie and need a lecture on the basics?
;)

[Hugo Kornelis]

On 12 May 2006 12:46:09 -0700, Alexander Kuznetsov wrote:

Daniel,

you failed to notice a loud and clear ORDER BY clause in my script

SELECT * FROM T1 order by c1;

Hi Allexander,

Maybe keywords are case sensitive in Oracle??

<gd&r>

--
Hugo Kornelis, SQL Server MVP

[Alexander Kuznetsov]

Hi Hugo,

that's funny. Yep, in some cases case matters (pun intended), for
instance here is a DB2 UDB palindrome, credits to Serge RIelau

create table where(where char(1))

And the palindrome itself, a valid query:

select where from where select

select
where /*column name*/
from
where /* table name */
select /* table alias */

;)

[Erland Sommarskog]

Brian Peasland (or********@nos pam.peasland.ne t) writes:

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.

Yes, having both "Employees" and "employees" in a database is bad idea.
That does not mean that it is a bad idea to have case-sensitive object
names. The issue you raise is fairly hypothetical.

The one that I and Tony raise is real. I can tell from own experience.
My main instances of SQL Server runs a case-sensitive collation. I
frequently copy scripts from newsgroup posts to help people find
solutions. I often have to spend quite some time of cleaning up
inconsistent case usage.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

Erland,

on one hand, I would concur: in most cases we need case insensitive
data in the database. For instance, we in our shop uppercase all the
data when we load it into Oracle. On the other hand, I think that case
sensitive indexes may be implemented more efficiently than case
insensitive ones, just because binary (case sensitive) comparisons are
the fastest ones possible. However I am not sure what the performance
gain might be...