Very Computer

we are converting applications from Sybase to ss2k. Our applications
were developed with case sensitive settings.

The default setting for ss2k is case insensitive.  

What is the advantage of taking the default install, and using case
insensitive collations?

Are there performance advantages?

What about for connecting with other servers?

What are other installations doing?

Thanks.

Linda

Standard SQL is case sensitive, so you shoudl always use that setting.  

--CELKO--

SQL guru at Trilogy
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Linda,

The advantages really depend on how you will use the data. I would guess
that most sites are Case Insensitive (Default). Some of the advantages are
as follows:

You don't have to worry about what case the data was originally input in to
do a search. If it were Case Sensitive you would always have to use UPPER(),
LOWER() etc to make sure of an exact match.
The data potentially looks better than storing it all upper or lower case.
Search's are a little faster if used as is. They can be a lot slower if you
have to use UPPER() etc(depends on indexes).

But the bottom line is what is the requirement of your application? If you
need it to be case sensitive then these are really non issues, make it case
sensitive.

--
Andrew J. Kelly
Targitmail.com

Quote:> we are converting applications from Sybase to ss2k. Our applications
> were developed with case sensitive settings.

> The default setting for ss2k is case insensitive.

> What is the advantage of taking the default install, and using case
> insensitive collations?

> Are there performance advantages?

> What about for connecting with other servers?

> What are other installations doing?

> Thanks.

> Linda

Linda,

The most important thing you need to consider is what your users want and
need.  If they are like most people and expect case-insensitive sorting and
searching, then you need to use the case-insensitive settings.  What's been
done in the past or what some standard dictates is irrelevant, if this is
not what your users want.

If for some reason they don't care one way or the other, then I still
recommend case-insensitive settings for the reasons Andrew cited.  The
performance aspect especially should not be underestimated on large tables.
In addition, by avoiding all that messy conversion to and from upper or
lower case, you simplify your code, which helps keep it more maintainable
over time.

Although Oracle is a good product, it does not support case-insensitive
sorting and searching.  From working with Oracle I have observed that
programming errors stemming from this "deficiency" are frequent, subtle, and
hard to find.  You can effectively prevent a whole class of troublesome
errors from ever occurring by using the case-insensitive settings.

Linda

Quote:> we are converting applications from Sybase to ss2k. Our applications
> were developed with case sensitive settings.

> The default setting for ss2k is case insensitive.

> What is the advantage of taking the default install, and using case
> insensitive collations?

> Are there performance advantages?

> What about for connecting with other servers?

> What are other installations doing?

> Thanks.

> Linda

But almost nobody is using case sensitive collations (doesn't do its job) and SQL 99
(yeah, I know that you don't like the -99 standard) allow for case insensitive
collations.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

Quote:> Standard SQL is case sensitive, so you shoudl always use that setting.

> --CELKO--

> SQL guru at Trilogy
> ===========================
> Please post DDL, so that people do not have to guess what the keys, constraints,

Declarative Referential Integrity, datatypes, etc. in your schema are.

Quote:

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Linda,

Quote:> Although Oracle is a good product, it does not support case-insensitive
> sorting and searching.

That is interesting, I don't know that.
Do you know is this has changed in (recent) version of Oracle?
(Reason for asking is not to hit Oracle, but to better understand what viewpoint
someone with background from other systems has.)

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

Quote:> Linda,

<snip>

Tibor,

I haven't worked with Oracle in a while, so I don't know what the latest
version (Oracle9i?)does, but I believe this hasn't changed.  On any Oracle
project that I have worked on, the UPPER() function became my best friend.
:-)  As you can imagine, if you are storing your data in mixed case, you
need to convert column values AND variables for just about every string
comparison and restriction to get accurate results.  It's an even worse
headache than nulls! :-)

I don't know why people prefer UPPER() to LOWER().  I guess it must the the
old COBOL influence.

I have Oracle books at home covering 8i that recommend changing all input
data to uppercase to avoid these problems, so that's what most people do.
It's the standard approach to getting "correct" sorts.  That explains a lot
of those ugly, old-fashioned looking credit card and utility bills we are
still seeing in this day and age.

Linda

Thanks for sharing, Linda.

I've been in a couple of projects where the code had to work on both CS and CI SQL
Servers, so I know about the pain and what extra bugs you get when working on a CS
server.

Funny you mention preferring UPPER() to LOWER(). I noticed that both me and a
colleague used UPPER() and I thought about that. I even thought about also using LOWER
(just for fun), but it felt too weird for some reason. And I can't blame Cobol for
that since I didn't do Cobol...

About converting all input to uppercase. I take it (reading your notes) that we then
would accept that we won't get back "propercase". (I doubt that we can find algorithms
smart enough to handle sir-names, for instance (Mac, Mc, van, van der, von, etc.).)
I know that if I were to decide about our next course reservation system, I would not
accept such behavior...
:-)
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

Tibor,

Quote:> Funny you mention preferring UPPER() to LOWER(). I noticed that both
> me and a colleague used UPPER() and I thought about that. I even
> thought about also using LOWER (just for fun), but it felt too weird
> for some reason. And I can't blame Cobol for that since I didn't do
> Cobol...

Well, then maybe it's an artifact from the era of steam computing, when
teletype consoles and chain printers didn't even have any lowercase
characters. :-)  But if you think LOWER() feels strange, when was the last
time you tried to do a RIGHT JOIN instead of a LEFT JOIN.  Now that's weird!

Quote:> About converting all input to uppercase. I take it (reading your
> notes) that we then would accept that we won't get back "propercase".
> I know that if I were to decide about our next course reservation
> system, I would not accept such behavior... :-)

Oh, I agree.  The original style and case of the text are lost.  As a user I
would find it completely unacceptable if the software took it upon itself to
mangle my data and change it to what it thought I "ought" to have entered.
:-)

I can't think of a need for case-sensitive sort for normal business
applications, but I guess there are a few oddball cases where it might be
needed.

It seems to me that for such cases where you do need a case-sensitive sort
order, creating a whole case-sensitive instance or database is really
overkill.  Couldn't that be solved in a more elegant manner using a COLLATE
clause on the columns where we need it?  I haven't worked with this at all
(we're still on 7.0) so I don't know, but I'm sitting here looking at SQL
Server 2000 BOL "Collations" topic and p. 159 of "SQL-99 Complete, Really"
and that all appears to be supported.  Are there any issues with this
approach in practice that are not apparent?

Linda W.

Linda,

Hehe, I agree on that RIGHT JOIN issue. I don't think I've ever wrote one :-).

Quote:> It seems to me that for such cases where you do need a case-sensitive sort
> order, creating a whole case-sensitive instance or database is really
> overkill.  Couldn't that be solved in a more elegant manner using a COLLATE
> clause on the columns where we need it?

Absolutely!

Quote:> I haven't worked with this at all
> (we're still on 7.0) so I don't know, but I'm sitting here looking at SQL
> Server 2000 BOL "Collations" topic and p. 159 of "SQL-99 Complete, Really"
> and that all appears to be supported.

I haven't actually compared syntax between BOL and SQL-99, but I assume that this was
such a late feature so that MS could implement it as per SQL-99.

Quote:> Are there any issues with this
> approach in practice that are not apparent?

A few things to consider (might be apparent).
If you correlate/compare data between collations, SQL server might then not know how
to resolve that comparison. I.e., you might have to specify COLLATE in the query
(event though you might "not care"). A couple of examples:

Cross db (collation) references. A user might think, during installation, that
selecting collation isn't a big issue, since this can be selected at db level. But
this will mean that we have a different collation in system databases then the user
databases. Cross references might then need the COLLATE keyword in the query. System
proc's, for instance.

Temp tables. A temptable created with SELECT INTO will inherit collation from result
set. But a temp table created with CREATE TABLE will inherit tempdb's collation (Kalen
showed me that we can use "database_default" as collation to get collation from
current database). Below is a script that exemplifies this (you probably don't have to
execute to understand, see comments).

--Create a database with a different collation than server default collation
CREATE DATABASE [TestDb]  COLLATE Albanian_BIN
USE TestDb

--Create permanent table from which we will draw data and check collation
CREATE TABLE Permanent_(c1 CHAR(5))
SELECT collation
 FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
 WHERE so.name like 'Permanent_'

--Create temp table with CREATE TABLE.
--Table will inherit tempdb's collation
CREATE TABLE #CT_(c1 CHAR(5))
SELECT collation
 FROM tempdb..sysobjects so INNER JOIN tempdb..syscolumns sc ON so.id = sc.id
 WHERE so.name like '%#CT_%'

--Create temp table with CREATE TABLE.
--By using the "database_default" collation,
--you can inherit collation from current database
CREATE TABLE #CT_dd_(c1 CHAR(5) COLLATE database_default)
SELECT collation
 FROM tempdb..sysobjects so INNER JOIN tempdb..syscolumns sc ON so.id = sc.id
 WHERE so.name like '%#CT_dd_%'

--Create temp table with SELECT INTO.
--Table will inherit *resultsets* collation
USE master
GO
SELECT * INTO #SI FROM TestDb..t1
SELECT collation
 FROM tempdb..sysobjects so INNER JOIN tempdb..syscolumns sc ON so.id = sc.id
 WHERE so.name like '%#SI%'
GO

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.