18

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

S.L. Barth is on codidact.com
  • 8,198
  • 71
  • 51
  • 66
leora
  • 188,729
  • 360
  • 878
  • 1,366

2 Answers2

39

Accent Sensitive and Accent Insensitive

Lòpez and Lopez are the same if Accent Insensitive.

Erik
  • 88,732
  • 13
  • 198
  • 189
  • thanks . . is there a database setting that will set one or the other as the default ?? – leora Mar 13 '11 at 22:35
  • @ooo: There's a collation tab in the server settings – Erik Mar 13 '11 at 22:38
  • 1
    @ooo: A database can have its own default collation too (distinct from the server's default). See the Options group in the database properties. – Andriy M Mar 13 '11 at 22:48
4

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.

adrianbanks
  • 81,306
  • 22
  • 176
  • 206