Tuesday, August 3, 2010

Installing SQL Server 2005: Collation

I had to make sure I had the collation settings set exactly as they were in a production instance of SQL Server. In the past, SQL Server gave you a single list of collation settings - code orders to pick from. I think the default was:

"SQL_Latin1_General_Cp1_CI_AI" ("sort order id" 54).

But since SQL Server 2005, the preferred way to set collation is now modular: you first select a "Collation designator" (i.e., alphabet) and then specify the "sort order" by selecting from various checkboxes:

  • binary
  • case-sensitve
  • accent-sensitive
  • binary-code point
  • kana sensitive
  • width-sensitive

So I had to figure out what the old collation setting string (in my case, "SQL Latin1_Cp1_CI_AS") meant, in this regard. As is kind of obvious, it contains the sort order settings as abbreviations. But just to make sure:

Code Page
Specifies a one- to four-digit number that identifies the code page used by the collation. 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.

Case Sensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

Accent Sensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

No comments: