The Dixie Flatline (3/24/2010)
Paul, I have to ask just when is Windows collation used? I would assume that since all the work is being done inside SQL that some default SQL collation sequence would be used, and you say that performance is not at all bad with SQL collation. Doesn't each server, and DB have a default collation setting?
The collation rules are quite complex, see Collation Precedence.
There is no 'internal collation' in the way I think you mean. The collation used in comparisons and sorts always depends on the collation of the data involved, whether explicit or implicit. The link above provides the details.
Just in case it is not obvious, SQL collations all start with SQL_, binary collations all end with BIN or BIN2, and the others are all Windows collations. That statement is mostly true, anyway 😉
Latin1_General_CI_AS is an example of a Windows collation.
SQL_Latin1_General_CP1_CI_AS is a SQL collation.
Latin1_General_BIN is a binary code point collation.