Setting Expression Collation
Parameters for stored procedures or functions, alias data types, and variables are assigned the default collation of the database. To change the collation of an alias type, you must drop the alias and re-create it.
You can override the default collation of a literal string by using the COLLATE clause. If you do not specify a collation, the literal is assigned the database default collation. You can use DATABASEPROPERTYEXto find the current collation of the database.
You can override the server, database, or column collation by specifying a collation in the ORDER BY clause of a SELECT statement. For more information, see Sorting Rows with ORDER BY.