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

[David Portas]

Re: SQL2005 collation vs Oracle

Nasir wrote:[color=blue]
> 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[/color]

Yes. In fact the collation for data is always determined at column
level. The database collation defines whether identifiers are
case-sensitive and is also the default for the column collation. Take a
look at the collations topics in Books Online.

CREATE TABLE t1(c1 CHAR(10) COLLATE Latin1_General_ CS_AS NOT NULL /*
.... */);
INSERT INTO t1 VALUES('Mike');
INSERT INTO t1 VALUES('mike');

SELECT * FROM T1 WHERE c1='Mike';

Result:

c1
----------
Mike

(1 row(s) affected)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Nasir]

Re: SQL2005 collation vs Oracle

David,

I installed 2005 server with SQL_Latin1_Gene ral_CP1_CS_AS in hope to
acheive what you described, but look what happens:

3> create table t1 (c1 char(10))
4> go
2> NSERT INTO t1 VALUES('mike')
3> go
(1 rows affected)
1> INSERT INTO t1 VALUES('mike')
2> go
(1 rows affected)

1> SELECT * FROM T1 WHERE c1='Mike'
2> go
Msg 208, Level 16, State 1, Invalid object name 'T1'.
1>
2> SELECT * FROM t1 WHERE c1='Mike'
3> go
c1
----------
Mike

(1 rows affected)
1>

I wonder if collation you mentioned Latin1_General_ CS_AS is available at
server level?

Thanks,
Nasir


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:1147377053 .596753.143460@ q12g2000cwa.goo glegroups.com.. .[color=blue]
> Nasir wrote:[color=green]
>> 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[/color]
>
> Yes. In fact the collation for data is always determined at column
> level. The database collation defines whether identifiers are
> case-sensitive and is also the default for the column collation. Take a
> look at the collations topics in Books Online.
>
> CREATE TABLE t1(c1 CHAR(10) COLLATE Latin1_General_ CS_AS NOT NULL /*
> ... */);
> INSERT INTO t1 VALUES('Mike');
> INSERT INTO t1 VALUES('mike');
>
> SELECT * FROM T1 WHERE c1='Mike';
>
> Result:
>
> c1
> ----------
> Mike
>
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
> --
>[/color]

[David Portas]

Re: SQL2005 collation vs Oracle

Nasir wrote:[color=blue]
> David,
>
> I installed 2005 server with SQL_Latin1_Gene ral_CP1_CS_AS in hope to
> acheive what you described, but look what happens:
>[/color]

That is correct. The "CS" part of the collation name means
Case-Sensitive. So you need to change it to Insensitive if you don't
require case-sensitive identifers. You can still specify a CS collation
for the column:

ALTER DATABASE junk COLLATE Latin1_General_ CI_AS ;

CREATE TABLE t1(c1 CHAR(10) COLLATE Latin1_General_ CS_AS NOT NULL /*
.... */);
INSERT INTO t1 VALUES('Mike');
INSERT INTO t1 VALUES('mike');

SELECT * FROM T1 WHERE c1='Mike';

The collations beginning with SQL_ exist for backwards-compatibility
reasons. The Windows collations are preferred unless compatibility with
version 7.0 is required.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: SQL2005 collation vs Oracle

Nasir (nmajeed@prosrm .com) writes:[color=blue]
> I installed 2005 server with SQL_Latin1_Gene ral_CP1_CS_AS in hope to
> acheive what you described, but look what happens:[/color]

David's post may require some clarification.

In SQL Server you can set collation on three levels:

1) Server
2) Database default
3) Column level

To simplify, let's assume that server and database default are the
same.

If you want table names to be case-insensitive (which is a bad idea in
my opinion), the you install the server with a case-insensitive collation,
such SQL_Latin1_Gene ral_CP1_CI_AS or Latin1_General_ CI_AS.

Then you create every table character column to be case-sensitive:

CREATE TABLE t1 (c1 char(10) COLLATE Latin_General_C S_AS NOT NULL)

Obviously, this is quite messy, since you need to specify the collation
for every column.


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

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: SQL2005 collation vs Oracle

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> The collations beginning with SQL_ exist for backwards-compatibility
> reasons. The Windows collations are preferred unless compatibility with
> version 7.0 is required.[/color]

One like to think so, but US English users are still offered an SQL
collation as a default when they install SQL 2005!


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

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Nasir]

Re: SQL2005 collation vs Oracle

So going back to my original question - seems like it really does not
support at the server level setting of SQL 2005, to achieve table and column
names insensitive and chacter data to be sensitive. Is that correct?

To get the character data sensitive I have to modify each column with
collate, what a mess! I'm surprised that so many people are OK/fine with
Oracle way of dictionary insensitve and data sensitive, but it's so hard to
get thatin SQL. There got to be a btter way!


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97C1E7D 5DA6Yazorman@12 7.0.0.1...[color=blue]
> Nasir (nmajeed@prosrm .com) writes:[color=green]
>> I installed 2005 server with SQL_Latin1_Gene ral_CP1_CS_AS in hope to
>> acheive what you described, but look what happens:[/color]
>
> David's post may require some clarification.
>
> In SQL Server you can set collation on three levels:
>
> 1) Server
> 2) Database default
> 3) Column level
>
> To simplify, let's assume that server and database default are the
> same.
>
> If you want table names to be case-insensitive (which is a bad idea in
> my opinion), the you install the server with a case-insensitive collation,
> such SQL_Latin1_Gene ral_CP1_CI_AS or Latin1_General_ CI_AS.
>
> Then you create every table character column to be case-sensitive:
>
> CREATE TABLE t1 (c1 char(10) COLLATE Latin_General_C S_AS NOT NULL)
>
> Obviously, this is quite messy, since you need to specify the collation
> for every column.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@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[/color]

[David Portas]

Re: SQL2005 collation vs Oracle

Nasir wrote:[color=blue]
> So going back to my original question - seems like it really does not
> support at the server level setting of SQL 2005, to achieve table and column
> names insensitive and chacter data to be sensitive. Is that correct?
>
> To get the character data sensitive I have to modify each column with
> collate, what a mess! I'm surprised that so many people are OK/fine with
> Oracle way of dictionary insensitve and data sensitive, but it's so hard to
> get thatin SQL. There got to be a btter way!
>[/color]

If you use design tools and scripts to generate schemas (as surely most
of us do) then the fact that the schema is part of the column
definition shouldn't cause you any problem. You are really looking for
a syntax shortcut. You can always propose it at:

http://lab.msdn.microsoft.com/ProductFeedback/

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: SQL2005 collation vs Oracle

Nasir (nmajeed@prosrm .com) writes:[color=blue]
> So going back to my original question - seems like it really does not
> support at the server level setting of SQL 2005, to achieve table and
> column names insensitive and chacter data to be sensitive. Is that
> correct?
>
> To get the character data sensitive I have to modify each column with
> collate, what a mess! I'm surprised that so many people are OK/fine with
> Oracle way of dictionary insensitve and data sensitive, but it's so hard
> to get thatin SQL. There got to be a btter way![/color]

Yes, if you try to use SQL Server as if it was Oracle, or vice versa, it
will be very painful.

I can agree that Oracle's way has a point - but in such case I would use
it the other way round: data case-insensitive, metadata case-sensitive.

My strong recommendation is that for development you should use case-
sensitive collation. Because if you develop under case-insensitive, and
the customer insists on case-sensitive, and you have used case
inconsistently, it will be very difficult to sort out. A collorary is that
it's a good idea to stick with all lowercase for names, so you don't end up
with both t1 and T1.

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

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Mark D Powell]

Re: SQL2005 collation vs Oracle

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

[Tony Rogerson]

Re: SQL2005 collation vs Oracle

>I think Oracle got it right. It should not matter if you use MYTABLE,[color=blue]
> 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[/color]

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" <Mark.Powell@ed s.com> wrote in message
news:1147449278 .247001.136270@ i39g2000cwa.goo glegroups.com.. .[color=blue]
>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 --
>[/color]

[Alexander Kuznetsov]

Re: SQL2005 collation vs Oracle

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]

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.
[color=blue]
> collation should reflect the
> real world if a salesman shouts out AKA do they mean aka, Aka, AKa, AKA or
> what????[/color]

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.
[color=blue]
> 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.[/color]

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.
[color=blue]
> Case Insensitive is how the real world works and that should be the default
> for any database 'period'.[/color]

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.
[color=blue]
> As for Object names it shouldn't matter but case insenitive promotes poor
> programming practice.[/color]

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.
[color=blue]
> Consider portability to other products and systems.....[/color]

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@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]

Re: SQL2005 collation vs Oracle

> Agreed with your example, but in the real world, case can matter for some[color=blue]
> 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.[/color]

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)???
[color=blue]
> 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.[/color]

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" <oracle_dba@nos pam.peasland.ne t> wrote in message
news:Iz5vJz.9p4 @igsrsparc2.er. usgs.gov...[color=blue][color=green]
>> As for data Oracle has it completely wrong,[/color]
>
> 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.
>[color=green]
>> collation should reflect the real world if a salesman shouts out AKA do
>> they mean aka, Aka, AKa, AKA or what????[/color]
>
> 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.
>[color=green]
>> 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.[/color]
>
> 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.
>[color=green]
>> Case Insensitive is how the real world works and that should be the
>> default for any database 'period'.[/color]
>
> 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.
>[color=green]
>> As for Object names it shouldn't matter but case insenitive promotes poor
>> programming practice.[/color]
>
> 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.
>[color=green]
>> Consider portability to other products and systems.....[/color]
>
> 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@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[/color]

[DA Morgan]

Re: SQL2005 collation vs Oracle

Alexander Kuznetsov wrote:[color=blue]
> 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
>[/color]

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
damorgan@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group

https://www.psoug.org