Briefly stated: As a Windows DBA in the US/English locale, is it worth setting Latin1_General_CI_AS as your system-level default on new SQL server builds, or is it wiser to stick with the SQL_Latin1_General_CP1_CI_AS default for the foreseeable future? Do we think MS will eventually make Latin1_General_CI_AS the default in the US?
More background:
Per Microsoft, if your Windows locale is any English speaking country (except the US) your default SQL server collation during setup is: Latin1_General_CI_AS, but in the US it is still SQL_Latin1_General_CP1_CI_AS for backward compatibility. New databases take the system collation by default (unless you override), etc...
We have kept the SQL_Latin1_General_CP1_CI_AS as the default on our own SQL server builds, and our in-house databases have evolved with that same collation. We just received our first database with Latin1_General_CI_AS from a third party and it got me to question if we should be getting behind the "newer" way of collating, especially if we cross join or interact between databases in the same environment.
I know we can do collate commands for joins between the different databases, but I was curious if folks are preemptively configuring Latin1_General_CI_AS as their server level default in US English locale to be more compatible with folks in other countries or to get ahead of any possible "final pivot" on Microsoft's side in future Windows builds.
Thanks!
CodePudding user response：
I cannot speak to a future change in the installation default but, having worked in organizations with varying instance collations, I suggest you stick with whatever collation is the standard for your organization (including de-facto one). This is is typically SQL_Latin1_General_CP1_CI_AS in the US since it's the installation default.
Standardizing on a collation facilitates instance consolidation, replication, etc. Third party applications may require a different collation.
CodePudding user response：
Briefly stated: As a Windows DBA in the US/English locale, is it worth setting Latin1_General_CI_AS as your system-level default on new SQL server builds
Briefly answered: no.
The only time you're going to care about the instance collation is when an existing workload breaks because of it. And sticking with the old SQL collation instead of the better-and-more-modern Windows collation is the lower-risk path for DBAs.
Individual databases can adopt Latin1_General_CI_AS and accept that tempdb will use SQL_Latin1_General_CP1_CI_AS, or can adopt partial database containment and get contained database collation.