SQL2005 collation vs Oracle

**Alexander Kuznetsov** · May 12 '06, 07:55 PM

Re: SQL2005 collation vs Oracle

Daniel,

you failed to notice a loud and clear ORDER BY clause in my script
[color=blue]
>SELECT * FROM T1 order by c1;[/color]

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

**Hugo Kornelis** · May 12 '06, 09:15 PM

Re: SQL2005 collation vs Oracle

On 12 May 2006 12:46:09 -0700, Alexander Kuznetsov wrote:
[color=blue]
>Daniel,
>
>you failed to notice a loud and clear ORDER BY clause in my script
>[color=green]
>>SELECT * FROM T1 order by c1;[/color][/color]

Hi Allexander,

Maybe keywords are case sensitive in Oracle??

<gd&r>

--
Hugo Kornelis, SQL Server MVP

**Alexander Kuznetsov** · May 12 '06, 09:45 PM

Re: SQL2005 collation vs Oracle

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** · May 12 '06, 10:05 PM

Re: SQL2005 collation vs Oracle

Brian Peasland (oracle_dba@nos pam.peasland.ne t) writes:[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]

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

**Alexander Kuznetsov** · May 12 '06, 10:15 PM

Re: SQL2005 collation vs Oracle

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

**Erland Sommarskog** · May 12 '06, 10:25 PM

Re: SQL2005 collation vs Oracle

Alexander Kuznetsov (AK_TIREDOFSPAM @hotmail.COM) writes:[color=blue]
> 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...[/color]

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

**DA Morgan** · May 12 '06, 11:15 PM

Re: SQL2005 collation vs Oracle

Alexander Kuznetsov wrote:[color=blue]
> Daniel,
>
> you failed to notice a loud and clear ORDER BY clause in my script
>[color=green]
>> SELECT * FROM T1 order by c1;[/color]
>
> and decided I'm a newbie and need a lecture on the basics?
> ;)[/color]

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

https://www.psoug.org

Oracle PL/SQL examples, syntax, DBMS packages, string, timestamp, substring, PHP code, and Javascript Code Reference Library (formerly known as Morgan's Library)

**Alexander Kuznetsov** · May 13 '06, 03:45 AM

Re: SQL2005 collation vs Oracle

> 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** · May 13 '06, 06:05 PM

Re: SQL2005 collation vs Oracle

> 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** · May 13 '06, 07:15 PM

Re: SQL2005 collation vs Oracle

Alexander Kuznetsov (AK_TIREDOFSPAM @hotmail.COM) writes:[color=blue][color=green]
>> Note that binary <> case-sensitive in SQL Server.[/color]
>
> 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?[/color]

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, 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** · May 15 '06, 03:15 PM

Re: SQL2005 collation vs Oracle

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_TIREDOFSPAM @hotmail.COM> wrote in message
news:1147491491 .272900.168260@ y43g2000cwc.goo glegroups.com.. .[color=blue][color=green]
>> With an ORDER BY the result > set is identical.[/color]
>
> 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
>[/color]

**Tony Rogerson** · May 15 '06, 08:45 PM

Re: SQL2005 collation vs Oracle

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" <nmajeed@prosrm .com> wrote in message
news:44689aae$0 $1010$39cecf19@ news.twtelecom. net...[color=blue]
> 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_TIREDOFSPAM @hotmail.COM> wrote in message
> news:1147491491 .272900.168260@ y43g2000cwc.goo glegroups.com.. .[color=green][color=darkred]
>>> With an ORDER BY the result > set is identical.[/color]
>>
>> 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
>>[/color]
>
>[/color]

**Erland Sommarskog** · May 15 '06, 09:45 PM

Re: SQL2005 collation vs Oracle

Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=blue]
> 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>.[/color]

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

SQL2005 collation vs Oracle

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment