Solved

Collation setting

Posted on 2009-05-12
8
1,182 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
 

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now