469,909 Members | 1,757 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

Collation Questions SQL Server 2005 SP2

aj
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
Oct 10 '07 #1
3 9482
aj (ro****@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, es****@sommarskog.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
Oct 10 '07 #2
aj
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 (ro****@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.

Oct 11 '07 #3
aj (ro****@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, es****@sommarskog.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
Oct 11 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

27 posts views Thread by Nasir | last post: by
3 posts views Thread by Nasir | last post: by
2 posts views Thread by Ryan | last post: by
2 posts views Thread by =?utf-8?B?UMSBdmVscyBNaWhhaWxvdnM=?= | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.