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

Oct 10 '07 #1

Subscribe Reply

9694

Erland Sommarskog

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

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

Erland Sommarskog

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

Similar topics

3736

Client/server tip - inconsistent collation order betw. Access & Server

by: Steve Jorgensen | last post by:

Hi all, Frankly, this is such an off-beat thing, I don't know if anyone else here will ever have the same issue. Just in case anyone does, though... I needed to be able to search for the first string in a recordset that is the same as a specified string value, or the one that would be right after that if it doesn't exist. Several...

Microsoft Access / VBA

85113

Cannot resolve collation conflict for equal to operation.

by: Ryan | last post by:

I've stumbled across the above error and am a little stuck. I've just installed SQL2000 (sp3) on my PC and restored a database from our Live server. On a simple Update statement on a #temp table, it fails with the above message. I think I understand what it means and found some old posts suggesting using the following : select name,...

Microsoft SQL Server

9919

How do I change the default collation string of an installation

by: David Greenberg | last post by:

Hi When we installed SqlServer2000 we left the default collation name (Sql_Latin1_General_CPI_CI_AS). The user defined databases we created afterwards were defined with a different collation name in order to be able to accept the character set we use, Hebrew. We are looking into switching DTSs that we use to copy data from our main system ,...

Microsoft SQL Server

27988

SQL Server 2005: Collation Conflict Error when selecting Database Properties

by: Peter Nurse | last post by:

I have just upgraded to SQL Server 2005 from SQL Server 2000. In Microsoft SQL Server Management Studio, when I click on database properties, I receive the following error:- Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)

Microsoft SQL Server

16461

SQL2005 collation vs Oracle

by: Nasir | last post by:

Hi there, Is it posible to set the collation of SQL server 2005 like Oracle, which is that objects names and column names are case-insensitive, but data is sensitive;e.g: In Oracle: --create lower-case table and column name create table t1(c1 char(10)); --Mike with upper-case J

Microsoft SQL Server

6385

Collation problem - urgent help

by: Nasir | last post by:

What is the best way to resolve these collation errors: My server is installed as - Latin1_General_CS_AS, but one of the important 50GB databases migrated from another server has - SQL_Latin1_General_CP1_CS_AS, and we are getting errors: Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between...

Microsoft SQL Server

4647

SQL Collation Problem

by: Ryan | last post by:

I have a large (ish) number of databases that various people where I work have created over the last few years. The problem is that no-one here really understands collations and a mistake made originally keeps cropping up and causing problems depending on where the create table script has come from. What happens is that someone needs to...

Microsoft SQL Server

7063

MS SQL Linked Servers(Linking MySQL to MS SQL) Collation problem.

by: =?utf-8?B?UMSBdmVscyBNaWhhaWxvdnM=?= | last post by:

Hello, I have a problem. I've linked MySql server to MsSql, in MySql I have a table with Latvian data(character set is ucs2, ucs2_general_ci) and the problem is that when I use openquery to read data from MySQL server, some characters are not translated correctly! I receive question symbols instead of Latvian special characters. Maybe...

Microsoft SQL Server

4393

sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1

by: Yas | last post by:

Hello, I currently have Table1 and View1. View1 is a query from 2 or 3 tables that works fine on its own. However in my current query if I try to use it...something like... SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3 FROM View1 a JOIN Table1 b on a.col1 = b.col1

Microsoft SQL Server

7559

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

7123

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...

Windows Server

7542

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

5701

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...

Career Advice

5100

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...

Microsoft Access / VBA

4756

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3237

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

811

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

470

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General