Hello everyone,
For SQL Server 2005/2008, after some self-study, I am not sure whether my understanding about how character data type is physically stored is correct. Please help to review and comment. Here is my understanding, please review and feel free to correct me.
when using unicode character type (e.g. nvarchar), underlying character are stored in UCS-2 encoding (i.e. character is stored physically in the form of related UCS-2 defined bit sequences for the character). Collation defines comparison and sorting, no codepage in this situation;
when using non-unicode character type (e.g. varchar), underlying character are stored physically as bits defined in related code page defined by collation. So in this case, collation defines not only comparison/sorting, but also which codepage (character, bit value represents the character, and using the same bit value to store the character in database physical storage).
thanks in advance, George