Collation Questions SQL Server 2005 SP2

A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.

The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.

I wish to use a variation of this, SQL_Latin1_General_CP1_CS_AI
collation, but there is no such collation returned from
fn_helpcollations(). Also, if I try to use this collation in
a CREATE DATABASE stmt, SQLS yells about it.

I see that there is a Latin1_General_CS_AI. What effects are there
in using this collation? The SQL_* collations are SQL collations,
while non-SQL_* collations are Windows collations, yes? SQLS runs
only on Windows, so am I safe in using Latin1_General_CS_AI? What
does the CP1 in the SQL collation signify? Am I asking for trouble?
------------------------------------
Assuming that I set Latin1_General_CS_AI (or any other case-sensitive
collation) at the database level, I believe my DDL/DML for that database
also becomes case-sensitive. How can I specify that I want ONLY my data
access to be case-sensitive, and not my DDL/DML? I don't want to have
to remember to type "select * from MyCamelCase" when "mycamelcase"
should work.

Any help appreciated.

A new SQLS DBA..

aj

Re: Collation Questions SQL Server 2005 SP2

aj (ronald@mcdonalds.com) writes:
> A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.
>
> The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.

The default collation when you install SQL Server depends on your regional
settings. SQL_Latin1_General_CP1_CI_AS is what you get when your regional
settings is English (US).

> I see that there is a Latin1_General_CS_AI. What effects are there
> in using this collation? The SQL_* collations are SQL collations,
> while non-SQL_* collations are Windows collations, yes? SQLS runs
> only on Windows, so am I safe in using Latin1_General_CS_AI? What
> does the CP1 in the SQL collation signify? Am I asking for trouble?

You should be fine. About everywhere else in the world when you install SQL
Server, the default collation is a Windows collation. For instance, in my
case it's Finnish_Swedish_CI_AS (but I always change it to
Finnish_Swedish_CS_AS.)

Windows collations are drawn from Windows and Unicode, and the sorting for
varchar and nvarchar data is the same (save that nvarchar includes far more
characters). SQL collations on the other hand are completely different
for varchar and nvarchar. For varchar they are just an 8-bit character
set, while for nvarchar they are Unicode. The flip side of this is since
they for varchar only have 255 charcters, operations with varchar are
quite a bit faster with SQL collations than with Windows collations (save
binary collations). However, there are also potential for performance
disasters with SQL collations if you join varchar and nvarchar that
are less likly to occur with Windows collations.

> Assuming that I set Latin1_General_CS_AI (or any other case-sensitive
> collation) at the database level, I believe my DDL/DML for that database
> also becomes case-sensitive. How can I specify that I want ONLY my data
> access to be case-sensitive, and not my DDL/DML? I don't want to have
> to remember to type "select * from MyCamelCase" when "mycamelcase"
> should work.

You would have to set the database collation to be one that fits your
preference for identifiers and then explcitly set the collation for each
column to be case-sensitive.

My strong recommendation is that you should always develop on a
case-sensitive collation. If you develop on a case-insensitive collation,
and the customer then insists on case-sensitive, you may have a complete
mess to sort out.

Personally, I don't see the point of using MyCamelCase, if you don't
care to remember how you originally defined it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Collation Questions SQL Server 2005 SP2

Thanks for your response. This confirms what we suspected.

Do you have any idea why there is no SQL_Latin1_General_CP1_CS_AI
collation? Can I get it somewhere?

Erland Sommarskog wrote:
> aj (ronald@mcdonalds.com) writes:
>> A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.
>>
>> The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.
>
> The default collation when you install SQL Server depends on your regional
> settings. SQL_Latin1_General_CP1_CI_AS is what you get when your regional
> settings is English (US).
>
>> I see that there is a Latin1_General_CS_AI. What effects are there
>> in using this collation? The SQL_* collations are SQL collations,
>> while non-SQL_* collations are Windows collations, yes? SQLS runs
>> only on Windows, so am I safe in using Latin1_General_CS_AI? What
>> does the CP1 in the SQL collation signify? Am I asking for trouble?
>
> You should be fine. About everywhere else in the world when you install SQL
> Server, the default collation is a Windows collation. For instance, in my
> case it's Finnish_Swedish_CI_AS (but I always change it to
> Finnish_Swedish_CS_AS.)
>
> Windows collations are drawn from Windows and Unicode, and the sorting for
> varchar and nvarchar data is the same (save that nvarchar includes far more
> characters). SQL collations on the other hand are completely different
> for varchar and nvarchar. For varchar they are just an 8-bit character
> set, while for nvarchar they are Unicode. The flip side of this is since
> they for varchar only have 255 charcters, operations with varchar are
> quite a bit faster with SQL collations than with Windows collations (save
> binary collations). However, there are also potential for performance
> disasters with SQL collations if you join varchar and nvarchar that
> are less likly to occur with Windows collations.
>
>> Assuming that I set Latin1_General_CS_AI (or any other case-sensitive
>> collation) at the database level, I believe my DDL/DML for that database
>> also becomes case-sensitive. How can I specify that I want ONLY my data
>> access to be case-sensitive, and not my DDL/DML? I don't want to have
>> to remember to type "select * from MyCamelCase" when "mycamelcase"
>> should work.
>
> You would have to set the database collation to be one that fits your
> preference for identifiers and then explcitly set the collation for each
> column to be case-sensitive.
>
> My strong recommendation is that you should always develop on a
> case-sensitive collation. If you develop on a case-insensitive collation,
> and the customer then insists on case-sensitive, you may have a complete
> mess to sort out.
>
> Personally, I don't see the point of using MyCamelCase, if you don't
> care to remember how you originally defined it.
>
>

Re: Collation Questions SQL Server 2005 SP2

aj (ronald@mcdonalds.com) writes:
> Do you have any idea why there is no SQL_Latin1_General_CP1_CS_AI
> collation?

No. Maybe they never developed this combiniation for SQL collations, but got
it for free with Windows collation. fn_helpcollations tells me that there
are no CS_AI SQL collation at all.

> Can I get it somewhere?

No. There is support for adding collations.

If you feel strongly about it, you can submit a suggestion on
http://connect.microsoft.com/SqlServer/Feedback.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx