|
|
Posted by aj on 10/11/07 13:20
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.
>
>
Navigation:
[Reply to this message]
|