Solved

Collation setting

Posted on 2009-05-12
8
1,185 Views
Last Modified: 2012-05-06
We are running sql 2005 (cluster).  I notied that our dev servers collation is set to LATIN1_GENERAL_CI_AI, but production is set to SQL_LATIN1_GENERAL_SP1_CI_AS.

For what I could find:
  latin1_general = U.S english...
  CI_AI:  case-insensitive, accent insensitive, kana insensitive, width insensitive
  CI_AS: same as above, but accent sensitive.

What does the CP1 indicate and the 'SQL' on the production collation.
0
Comment
Question by:sqlnewbie08
  • 4
  • 4
8 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365250
It means it's a SQL Collation instead of a Windows Collation.

I'd really try to have them the same, because if you ever find yourself trying to compare a value from on to another, then you'll get errors.

Rob
0
 

Author Comment

by:sqlnewbie08
ID: 24365276
What about the CP1... what does that indicate.

We are going to be rebuilding the dev servers in the near future, at which time it will match production.  In the meantime, I believe we will have the databases collation altered to match the production collation setting.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365364
Code Page 1. There are many, referring to different languages.

Rob
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sqlnewbie08
ID: 24365388
so... the only difference between these two is the 'accent'.  Is that correct?

Regarding DEV, would you recommend having the databases collation altered to match production?
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365543
Yes, but they still won't compare nicely with each other.

And yes, i would.
0
 

Author Comment

by:sqlnewbie08
ID: 24365594
What do you mean they won't compare nicely with each...

If I make the change in DEV.  The server will have LATIN1_GENERAL_CI_AI and the Database will have SQL_LATIN1_GENERAL_SP1_CI_AS.  Would this cause a conflict?


0
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24371278
If you try to compare data that is in a different collation, you get an error.

For instance... if one entry is fiancee, and the other is the same but with an accent, how should it be treated? Same or different?

To avoid errors, you should always keep collations identical.

So it's definitely worth keeping the Dev environment the same as the Production one.

Rob
0
 

Author Closing Comment

by:sqlnewbie08
ID: 31580565
thank you
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question