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]

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

[Nasir]

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" <RE************ *************** *@acm.org> wrote in message
news:11******** **************@ q12g2000cwa.goo glegroups.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
--

[David Portas]

Nasir wrote:

David,

I installed 2005 server with SQL_Latin1_Gene ral_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
--

[Erland Sommarskog]

Nasir (nm*****@prosrm .com) writes:

I installed 2005 server with SQL_Latin1_Gene ral_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_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, 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

[Erland Sommarskog]

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

[Nasir]

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****@sommars kog.se> wrote in message
news:Xn******** ************@12 7.0.0.1...

Nasir (nm*****@prosrm .com) writes:

I installed 2005 server with SQL_Latin1_Gene ral_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_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, 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

[David Portas]

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

[Erland Sommarskog]

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

[Mark D Powell]

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