Menu
i am using SQL Compare to compare two versions of a database. it keeps highlighting differences in the nvarchar fields, where it shows one db that has:
Latin1_General_CI_AS
and the other one has this:
Latin1_General_CI_AI
can someone please explain what this is and if i should be worried about this difference
453
ON [Column X] = [Column X] COLLATE Latin1_General_CS_AS. A common use of the query level collation (collate SQL) is to compare case-sensitive strings. For example, imagine two tables with similar collation and compare their columns using join or subquery.
Default server-level collation is SQL_Latin1_General_CP1_CI_AS. If you are migrating databases from SQL Server to Managed Instance, check the server collation in the source SQL Server using SERVERPROPERTY(N'Collation') function and create a Managed Instance that matches the collation of your SQL Server.
CP1 specifies code page 1252, for all other code pages the complete code page number is specified. For example, CP1251 specifies code page 1251 and CP850 specifies code page 850. CaseSensitivity CI specifies case-insensitive, CS specifies case-sensitive.
These are definitely obsolete, even if not officially deprecated, and are mainly for pre-SQL Server 2000 compatibility. Although, quite unfortunately SQL_Latin1_General_CP1_CI_AS is very common due to it being the default when installing on an OS using US English as its language.
Accent Sensitive and Accent Insensitive
Lòpez and Lopez are the same if Accent Insensitive.
172
What the comparison is showing is that the two columns have difference collations. The collation of a (text) field affects how it is both stored and compared.
The particular difference in your case is that accents on characters will be ignored when comparisons and sorting is done.
When you install SQL Server, you set a default collation for the whole server. You can also set a collation per database and per column, meaning that you can mix them within a database (whether you want to depends on your particular case). The MSDN page I linked to has more information on collations, how to choose the best one, and how to set them.
26
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
