For a list and more information, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).
The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types.
The modified column cannot be any one of the following: Some data type changes may cause a change in the data.
For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters.
If it is incorrect, then you can get wrong results.
The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.
Modifies a table definition by altering, adding, or dropping columns and constraints, reassigning and rebuilding partitions, or disabling or enabling constraints and triggers.
Transact-SQL Syntax Conventions Is the name of the table to be altered.
If you add a column with a user-defined data type, we recommend that you define the column with the same nullability as the user-defined data type and specify a default value for the column.
If the table is not in the current database or is not contained by the schema owned by the current user, the database and schema must be explicitly specified.
ALTER COLUMN Specifies that the named column is to be changed or altered.
Before typing an xml column to a schema collection, the schema collection must first be created in the database by using CREATE XML SCHEMA COLLECTION.
COLLATE Specifies the new collation for the altered column.