SQL 2005 Collation Selection

Posted on 2011-10-29
Medium Priority
Last Modified: 2012-08-14
I ran sp_helpsort and got the following

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

I am trying to install on a new server and cannot get the correct combination and I do not know why.  

If someone would please tell me what to select on this screen (attached) I would be forever greatful.  I have tried everything and cannot get the above combination

Question by:KateHulit
  • 3
  • 2
  • 2

Author Comment

ID: 37050963
Forgot to attach  sorry
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 37052544
OK, there are a few differences you need to be aware of.

First, it looks like the above is the SQL Server collation, and a new version of SQL Server 2005 and above will be looking toward the WINDOWS collation (and helped by the default code page and regional settings).

Where as, if you upgraded the previous version, then it would have kept the SQL collations. There are subtle / slight differences.

If you plan to share and link the two then it is best to keep the same collation. If not, then best to use Windows collation for the new server.

Notice on the bottom half of the screen the radio button for SQL server collations ? They are the ones you will most likely want.

if you run :


you will get the name. For Windows collation (given the above) it will be something like : Latin1_General_CI_AI  (normally choose accent insensitive, but then again yours is showing as Accent sensitive "AS")

The SQL variant for the same (again given the options above) will be :  SQL_Latin1_General_Cp1_CI_AS_KI_WI  which has a sort id of 52

So, check the SQL COLLATION radio button and then look for the dictionary sort order that most closely aligns with the above (ie Dictionary order, case-insensitive
). That happens to be the one highlighted :)

LVL 23

Expert Comment

ID: 37052683
If you are not sure use Latin1_General_CI_AI
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 51

Expert Comment

by:Mark Wills
ID: 37052690
Well, yes, that would typically be the default english/US collation with AS, but if the other database has to "talk" then you will be forever using the "collation" hint if comparing (character based) columns (of course if the databases dont talk, then best to use the default collation as mentioned previously).

LVL 23

Expert Comment

ID: 37052705

True.  In regional communications, one has to be cautious about limiting the cast overhead to a minimum.  However when using distant communications through different regional settings, one can declaratively control casting (as opposed to applicatively) through the linked server set up configuration.  Read more in

LVL 51

Expert Comment

by:Mark Wills
ID: 37053020
ummmm.... thats not quite what I was saying. And yes, I am very much aware of the use of collations.

The "default" collation out of the box would be Accent Sensitive (for codepage 1252 at least) ie Latin1_General_CI_AS unless of course you happen to be US English where the default is SQL_Latin1_General_CP1_CI_AS (go figure, and had misrepresented the US in my previous post). But we digress...

On your current server in a query window run : SELECT SERVERPROPERTY ('Collation')

You will get a collation name. Match it with the SQL collation name in : http://msdn.microsoft.com/en-us/library/ms144250(v=SQL.90).aspx and it will point (in the middle column) to the collation you need to select to make it the same as you already have.

If your collation name does not have the SQL prefix, then please let us know.


Author Closing Comment

ID: 37053411
Thanks Mark.  So many options.  I finally got this to match.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

809 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