Make Latin1_General_(100)_CI_AS the default collation for US English
This description is identical with:
When installing SQL Server you need to select a server collation. However, Setup permits you to go with a default. This may seem convenient, but it often backlashes, because the default is unsuitable for a number of reasons.
A real-world case: I was contacted by a guy who was fearing for his job, if he could not get some speed out of his database. The query he show me was a simple lookup the PK. The problem was that the application (using Java in Websphere) worked with Unicode, but the table had varchar keys. And he is located in the US, he was using SQLLatin1GeneralCP1CI_AS, which is the default for English (United States). Had he been located elsewhere, he would probably have been using a Windows collation, and he would never had any performance issue. (Because for Windows collation, SQL Server can still seek the index when varchar meets nvarchar. Not so for an SQL collation.)
I have heard from people in other English-speaking countries like Australia and the UK,
that they have a mess of Latin1GeneralCIAS and SQLLatin1GeneralCP1CIAS, probably because sometimes the Windows admin change the system locale, and sometimes not. Then the person who install SQL Server does not know, and since there is a default,
he does not have to know other; he can go with the flow. The mess comes later.
And this is not going to be any better with the new 100 collations which are the default
with some system locales.
Upvotes: 10<=-=Aug 6 2008 10:15AM=-=>
Thank you for sharing your ideas. As you say this one is somewhat less revolutionary than not having a default collation at all; you’re also right that the major obstacle in doing it is a back-compat. That being said the idea is still being discussed. I will keep you informed on the results of the discussion.
I would be intrigued to know, what exactly is this backwards compatibility issue about? The only I can think of is existing scripts for unattended install, but I would expect such scripts to require modifications for new versions of SQL Server anyway. Is there something I have overlooked. I would appreaciate if you could details this, either here, or via private note over email.<=-=Mar 17 2011 11:24AM=-=>
Thank you for submitting this suggestion, but we’re trying to clean house and remove items we feel we will likely not address given their priority relative to other items in our queue. We believe it is unlikely that we will address this suggestion, and so we are closing it as �won�t fix�.
This cleaning will help us focus on the high-priority items that we feel need to get done, and we hope that it help provide better clarity to you about the issues we will (and won’t) address.
While we’ve tried to look at each of these items individually, this was a large effort, and so we may have erred in assessing this particular suggestion’s priority. If you feel that this is worth reconsidering, please feel free to re-file it and we will be happy to take another look.
Krzysztof Kozielczyk, Microsoft SQL Server
Solomon Rutzky commented
PLEASE move forward with this request. It is absolutely absurd that even with SQL Server 2019 in CTP 2.1 at the moment, the default Instance-level Collation for systems with an English OS locale is still SQL_Latin1_General_CP_CI_AS. That is a horrible Collation. There is no _good_ reason for not using the most recent equivalent Windows Collation: Latin1_General_100_CI_AS_SC (the _SC option to support Supplementary Characters came out with SQL Server 2012 and this issue was originally submitted in 2008).
Yes, there will be some minor backwards compatibility issues, but only for instances that are having existing DBs restored to them that already have the old SQL Server Collation. But that is a problem that diminishes over time as new projects adopt the newer (and much better) default Collation. By keeping with the same old SQL_Latin1_General_CP1_CI_AS Collation, Microsoft is ensuring that these problems INCREASE over time, making it harder and harder to move away from this old default as more and more projects are created with it because people didn't know better and that they should have chosen at least Latin1_General_100_CI_AS_SC if not some other Windows Collation that is more appropriate to their needs.