• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1192
  • Last Modified:

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.
0
sqlnewbie08
Asked:
sqlnewbie08
  • 4
  • 4
1 Solution
 
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
 
rob_farleyCommented:
Code Page 1. There are many, referring to different languages.

Rob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
rob_farleyCommented:
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
 
sqlnewbie08Author Commented:
thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now