Alexander Kuznetsov (AK_TIREDOFSPAM (AT) hotmail (DOT) COM) writes:
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 (AT) sommarskog (DOT) 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

Alexander Kuznetsov wrote:
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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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

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?

Alexander Kuznetsov (AK_TIREDOFSPAM (AT) hotmail (DOT) COM) writes:
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 (AT) sommarskog (DOT) 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

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_TIREDOFSPAM (AT) hotmail (DOT) COM> wrote

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 (AT) prosrm (DOT) com> wrote

Tony Rogerson (tonyrogerson (AT) sqlserverfaq (DOT) com) writes:
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 (AT) sommarskog (DOT) 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