By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

SQL2005 collation vs Oracle

P: n/a
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

May 11 '06 #1
Share this Question
Share on Google+
27 Replies


P: n/a
Nasir wrote:
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


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

May 11 '06 #2

P: n/a
David,

I installed 2005 server with SQL_Latin1_General_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" <RE****************************@acm.org> wrote in message
news:11**********************@q12g2000cwa.googlegr oups.com...
Nasir wrote:
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


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

May 11 '06 #3

P: n/a
Nasir wrote:
David,

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


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/m...S,SQL.90).aspx
--

May 11 '06 #4

P: n/a
Nasir (nm*****@prosrm.com) writes:
I installed 2005 server with SQL_Latin1_General_CP1_CS_AS in hope to
acheive what you described, but look what happens:


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_General_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_CS_AS NOT NULL)

Obviously, this is quite messy, since you need to specify the collation
for every column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 11 '06 #5

P: n/a
David Portas (RE****************************@acm.org) writes:
The collations beginning with SQL_ exist for backwards-compatibility
reasons. The Windows collations are preferred unless compatibility with
version 7.0 is required.


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, es****@sommarskog.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
May 11 '06 #6

P: n/a
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" <es****@sommarskog.se> wrote in message
news:Xn********************@127.0.0.1...
Nasir (nm*****@prosrm.com) writes:
I installed 2005 server with SQL_Latin1_General_CP1_CS_AS in hope to
acheive what you described, but look what happens:


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_General_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_CS_AS NOT NULL)

Obviously, this is quite messy, since you need to specify the collation
for every column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

May 11 '06 #7

P: n/a
Nasir wrote:
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!


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/m...S,SQL.90).aspx
--

May 12 '06 #8

P: n/a
Nasir (nm*****@prosrm.com) writes:
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!


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, es****@sommarskog.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
May 12 '06 #9

P: n/a
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 --

May 12 '06 #10

P: n/a
>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*********@eds.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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 --

May 12 '06 #11

P: n/a
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

May 12 '06 #12

P: n/a
> 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********@nospam.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
May 12 '06 #13

P: n/a
> 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********@nospam.peasland.net> 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********@nospam.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

May 12 '06 #14

P: n/a
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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
May 12 '06 #15

P: n/a
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?
;)

May 12 '06 #16

P: n/a
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
May 12 '06 #17

P: n/a
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 */

;)

May 12 '06 #18

P: n/a
Brian Peasland (or********@nospam.peasland.net) 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****@sommarskog.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
May 12 '06 #19

P: n/a
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...

May 12 '06 #20

P: n/a
Alexander Kuznetsov (AK************@hotmail.COM) writes:
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...


Note that binary <> case-sensitive in SQL Server. That is, a binary
collation is case-sensitive, however the reverse does not apply. My
standard collation is Finnish_Swedish_CS_AS, and it's binary. It co-
sorts V and W, Y and .and sorts in the right order.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 12 '06 #21

P: n/a
Alexander Kuznetsov wrote:
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?
;)


Not sure I saw the original script. But I did see a statement
indicating different behaviour between Oracle and SQL Server.
With an ORDER BY the result set is identical.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
May 12 '06 #22

P: n/a
> With an ORDER BY the result > set is identical.

Can you post versions of SQL Server and Oracle and operating systems
for which they are identical?

In fact it is a well known little obstacle in migrations between
Oracle and SQL Server. More to the point, immediately before posting I
ran the script and cut and pasted my results. Repeat, the results as
harvested from 2 live servers several hours ago are different:

SELECT * FROM T1 order by c1;

SQL Server:

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

(2 row(s) affected)

drop table t1;

The same script in Oracle running on UNIX (HP-UX) returns rows in a
different
order:

C1
----------
AAA
A_A

May 13 '06 #23

P: n/a
> Note that binary <> case-sensitive in SQL Server.

yes, but you need < and > comparisons to navigate an index. I guess
Finnish_Swedish_CS_AS collation is implemened as a function. As such,
it probably works a little bit slower than raw bytes comparison,
probably no big deal, just a little bit slower. Makes sence?

May 13 '06 #24

P: n/a
Alexander Kuznetsov (AK************@hotmail.COM) writes:
Note that binary <> case-sensitive in SQL Server.


yes, but you need < and > comparisons to navigate an index. I guess
Finnish_Swedish_CS_AS collation is implemened as a function. As such,
it probably works a little bit slower than raw bytes comparison,
probably no big deal, just a little bit slower. Makes sence?


Exactly how Finnish_Swedish_CS_AS is implemented I don't know, but
I don't think the difference is smaller with regards to Finnish_Swedish_BIN
than to Finnish_Swedish_CI_AI.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 13 '06 #25

P: n/a
Thank you all for your commencts on this - I didn't know it is so common and
relatively painful issue. Certainly, I wasn't expecting it, that ther eis no
easy solution to this.

My opinion is that table and column name should be insensitive by pretty
much 80/20 or yet better 99/1 rule (it's a new one:-), the 1 only when we
are looking for readability of the object or column name. To the contrary,
we always need to read the data (actual information) from these tables and
columns, so data got to be sensitive. I don't think people enjoy seeing
there names in funny cases like mIKe vs Mike, let alone that e.e...example
which I'm not aware of.

More importatntly, if it is such a pain then why not provide this as an
option in collation; if SQL server can give you so many other options, this
one can also be included - specially when SQL server is not the leader in DB
category.

I think adopting a standard is good - so I'll be forced to use
SQL_Latin1_General_CP1_CS_AS to keep all sensitive across the board to avoid
confusions.

chao,
Nasir


"Alexander Kuznetsov" <AK************@hotmail.COM> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
With an ORDER BY the result > set is identical.


Can you post versions of SQL Server and Oracle and operating systems
for which they are identical?

In fact it is a well known little obstacle in migrations between
Oracle and SQL Server. More to the point, immediately before posting I
ran the script and cut and pasted my results. Repeat, the results as
harvested from 2 live servers several hours ago are different:

SELECT * FROM T1 order by c1;

SQL Server:

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

(2 row(s) affected)

drop table t1;

The same script in Oracle running on UNIX (HP-UX) returns rows in a
different
order:

C1
----------
AAA
A_A

May 15 '06 #26

P: n/a
I think Nasir you've already been told several times how you can do this
with SQL Server.

I don't see a problem setting the database collation to case insensitive and
specifying at a column level the case sensitive option - you need only do it
once at CREATE TABLE time and its no more hassle then writing NOT NULL or
NULL, its COLLATE <collation name>.

I'd suggest you think this through a lot more, consider the problems with
data being case sensitive.

When you type in 'sql server' into google does it only bring back those
results that had 'sql server' in them or do they bring back the 'SQL Server'
ones too, or perhaps Oracle users have some mystical power that allows them
to sense correct case and type it correctly every time....

There is no benefit to case sensitivity unless you are enforcing it in the
real world and to my experience (19+ years of development) there are seldom
cases for case sensitive data.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Nasir" <nm*****@prosrm.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Thank you all for your commencts on this - I didn't know it is so common
and relatively painful issue. Certainly, I wasn't expecting it, that ther
eis no easy solution to this.

My opinion is that table and column name should be insensitive by pretty
much 80/20 or yet better 99/1 rule (it's a new one:-), the 1 only when we
are looking for readability of the object or column name. To the contrary,
we always need to read the data (actual information) from these tables and
columns, so data got to be sensitive. I don't think people enjoy seeing
there names in funny cases like mIKe vs Mike, let alone that e.e...example
which I'm not aware of.

More importatntly, if it is such a pain then why not provide this as an
option in collation; if SQL server can give you so many other options,
this one can also be included - specially when SQL server is not the
leader in DB category.

I think adopting a standard is good - so I'll be forced to use
SQL_Latin1_General_CP1_CS_AS to keep all sensitive across the board to
avoid confusions.

chao,
Nasir


"Alexander Kuznetsov" <AK************@hotmail.COM> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
With an ORDER BY the result > set is identical.


Can you post versions of SQL Server and Oracle and operating systems
for which they are identical?

In fact it is a well known little obstacle in migrations between
Oracle and SQL Server. More to the point, immediately before posting I
ran the script and cut and pasted my results. Repeat, the results as
harvested from 2 live servers several hours ago are different:

SELECT * FROM T1 order by c1;

SQL Server:

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

(2 row(s) affected)

drop table t1;

The same script in Oracle running on UNIX (HP-UX) returns rows in a
different
order:

C1
----------
AAA
A_A


May 15 '06 #27

P: n/a
Tony Rogerson (to**********@sqlserverfaq.com) writes:
I don't see a problem setting the database collation to case insensitive
and specifying at a column level the case sensitive option - you need
only do it once at CREATE TABLE time and its no more hassle then writing
NOT NULL or NULL, its COLLATE <collation name>.


Depends on how many tables you have. :-)

And you would have to set the server collation to be case-sensitive,
or else temp tables will be painful.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 15 '06 #28

This discussion thread is closed

Replies have been disabled for this discussion.