Mdthbs

Default collation type in SQL Server allows for indexing against case insensitive strings yet the case of the data is persisted. How does this actually work? I'm looking for the actual nuts and bolts, bits and bytes, or a good resource that explains it in detail.

create table casetest (fruitnames nvarchar(50) not null);

create unique index IX_fruitnames on casetest(fruitnames);



insert into casetest values ('apples');

insert into casetest values ('Pears');

-- this insert fails

insert into casetest values ('pears');



-- this yields 'Pears' as a result

select * from casetest (forceseek) where fruitnames = 'PEARS'



update casetest set fruitnames = 'pears' where fruitnames = 'pEArs'



-- this yields 'pears' as a result

select * from casetest (forceseek) where fruitnames = 'PEARS'

Questions About SQL Server Collations You Were Too Shy to Ask by Robert Sheldon covers how to use collation. It does not cover how collation works. I'm interested in how an index can be efficiently created/queried not caring about case, while simultaneously storing case data.

indexing against case insensitive strings yet the case of the data is persisted. How does this actually work?

This is actually not a SQL Server specific behavior, it's just how these things work in general.

So, the data is the data. If you are speaking about an index specifically, the data needs to be stored as it is else it would require a look-up in the main table each time to get the actual value, and there would be no possibility of a covering index (at least not for string types).

The data, either in the table/clustered index or non-clustered index, does not contain any collation / sorting info. It is simply data. The collation rules (locale/culture and sensitivities) is just meta data stored attached to the column and used when a sort operation is called, which would include the creation/rebuild of an index. The rules defined by the particular collation are used to generate sort-keys, which are binary representations of the string. These binary representations incorporate the linguistic rules (or none if a binary collation is used). The sort-keys are used to place the records in their proper order, but are not themselves stored in the index or table (and they aren't truly needed since they would merely be in the same order as the rows in the table or index anyway; or, at least I have not seen these values in the index and was told that they aren't stored, though I would think that storing them might make comparisons faster, but at the same time it would make the index larger and maybe not worth it in the end). But, the physical order of the index is just sorting, not comparison.

There are two types of collations: SQL Server and Windows.

SQL Server collations (those with names starting with SQL_) are the older, pre-SQL Server 2000 way of sorting/comparing (even though SQL_Latin1_General_CP1_CI_AS is still the installation default on US English OSes, quite sadly). In this older, simplistic, non-Unicode model, each combination of locale, code page, and the various sensitivities are given a static mapping of each of the characters in that code page. Each character is assigned a value to denote how it equates with the others. The comparison operation in this model goes character by character to determine equality based on these underlying values per each character. This is what mustaccio is describing in his answer. The only sensitivities that can be adjusted in these collations are: "case" and "accent" ("width", "kana type" and "variation selector" are not available). Also, these none of these collations support Supplementary Characters (which makes sense as those are Unicode-specific and these collations only apply to non-Unicode data). This approach applies only to VARCHAR data.

Windows collations (those with names not starting with SQL_) are the newer (starting in SQL Server 2000) way of sorting/comparing. In this newer, complex, Unicode model, each combination of locale, code page, and the various sensitivities are not given a static mapping. For one thing, there are no code pages in this model. This model assigns a default sort values to each character, and then each locale/culture can re-assign sort values to any number of characters. This allows multiple cultures to use the same characters in different ways. This does have the affect of allowing for multiple languages to be sorted naturally using the same collation if they do not use the same characters (and if one of them does not need to re-assign any values and can simply use the defaults).

{more to come here...still typing up the Windows collation portion--not simple or short :-( }

The comparison operation in this model goes character by character per each sensitivity. All sensitivities can be adjusted in these collations: "case", "accent", "width", "kana type", and "variation selector". Also, some of these collations (when used with Unicode data) support Supplementary Characters. This approach applies to both NVARCHAR data and non-Unicode VARCHAR data. It applies to non-Unicode VARCHAR data by first converting the value to Unicode internally, and then applying the sort/comparison rules.

Typically this is implemented using collation tables that assign a certain score to each character. The sorting routine has a comparator that uses an appropriate table, whether default or specified explicitly, to compare strings, character by character, using their collation scores. If, for example, a particular collation table assigns a score of 1 to "a" and 201 to "A", and a lower score in this particular implementation means higher precedence, then "a" will be sorter before "A". Another table might assign reverse scores: 201 to "a" and 1 to "A", and the sort order will be subsequently reverse. Yet another table might assign equal scores to "a", "A", "Á", and "Å", which would lead to a case- and accent-insensitive comparison and sorting.

Similarly, such a collation table-based comparator used when comparing an index key with the value supplied in the predicate.