Setting the collate on the table means when the order by or group by clause are used on that column that is what it will use by default, so it saves some typing
Performance there is no impact other than the speed difference between the different collations.
The data is not stored in that specific order. One key issue when setting collation on a column is collation derivation
The collation derivation of an expression can be implicit or explicit. This distinction affects how collations are combined when multiple different collations appear in an expression. An explicit collation derivation occurs when a COLLATE clause is used; all other collation derivations are implicit. When multiple collations need to be combined, for example in a function call, the following rules are used:
If any input expression has an explicit collation derivation, then all explicitly derived collations among the input expressions must be the same, otherwise an error is raised. If any explicitly derived collation is present, that is the result of the collation combination.
Otherwise, all input expressions must have the same implicit collation derivation or the default collation. If any non-default collation is present, that is the result of the collation combination. Otherwise, the result is the default collation.
If there are conflicting non-default implicit collations among the input expressions, then the combination is deemed to have indeterminate collation. This is not an error condition unless the particular function being invoked requires knowledge of the collation it should apply. If it does, an error will be raised at run-time.
There is one thing that is not documented or I can not find anything.
I do not know is what
does when a column has collation defined?
I assume it would use that collation which brings up another interesting issue question
what does it do when its a composite index with different collations between the columns. I image would follow the above rules.