Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Collation setting

Posted on 2009-05-12
8
Medium Priority
?
1,191 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

886 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