|
|
Posted by Erland Sommarskog on 10/10/07 21:33
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|