What are the exact differences between the SQL_Latin1_General_CP1_CS_AS
and Latin1_General_CS_AS
collations in SQL Server?
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
However here are the settings we typically recommend: Set the default collation to SQL_Latin1_General_CP1_CI_AS. Ensure that you have SQL Server running in Case Insensitive mode. We use NCHAR, NVARCHAR string types so all data is unicode, so no character set is specified.
According the SQL Server Books Online, the characters included in range searches depend on the sorting rules of the collation. Collation Latin1_General_CS_AS uses dictionary order so both upper and lower case characters of the specified range are included.
The documentation recommends SQL_Latin1_General_CP1_CI_AS as the recommended code page, but that is now considered obsolete in newer versions of MS SQL Server.
I'd add this as a comment, but I don't have enough reputation.
Anyway, see this question for details on these collations and how they relate:
Can SQL Server SQL_Latin1_General_CP1_CI_AS be safely converted to Latin1_General_CI_AS?
To avoid hijacking that answer entirely, the only real difference is the sort order for some characters differs between the two collations. Character representation does not appear to differ.
The only thing I would add is that SQL_Latin1_General_CP1_CS_AS is the default collation for the English (United States) locale, and Latin1_General_CS_AS is the default collation for any other English locale, such as English (United Kingdom).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With