Collation setting

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.
sqlnewbie08Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rob_farleyConnect With a Mentor Commented:
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
 
rob_farleyCommented:
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
 
sqlnewbie08Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rob_farleyCommented:
Code Page 1. There are many, referring to different languages.

Rob
0
 
sqlnewbie08Author Commented:
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
 
rob_farleyCommented:
Yes, but they still won't compare nicely with each other.

And yes, i would.
0
 
sqlnewbie08Author Commented:
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
 
sqlnewbie08Author Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.