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

[Erland Sommarskog]

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

[DA Morgan]

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

[Alexander Kuznetsov]

> 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

[Alexander Kuznetsov]

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

[Erland Sommarskog]

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

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_Gene ral_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.goo glegroups.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

[Tony Rogerson]

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_Gene ral_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.goo glegroups.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

[Erland Sommarskog]

Tony Rogerson (to**********@s qlserverfaq.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****@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