How to set collation of a column with SQL?
Originally, I created my SQL Server database on a local computer. I set its collation to Latin1_General_CI_AI and everything worked well. When I moved the finished work to the web hosting SQL Server, I encountered problem: they use a different database collation. So what can I do now?
To be more specific, I need Latin1_General_CI_AI, but they have Czech_CI_AS. These two differ significantly when comparing strings in Czech language (surprisingly I need latin1 general, not Czech, to get correct results.)
When I tried to change collation of my database, the server complained that I don't have user permission to do that. I tried to contact support desk, but no luck. Can I help myself?
I know that possibly each single table column can have its own collation, so maybe I should set up all my string columns to Latin1_CI_AI. But I don't know how to do this. I have only SQL access to the database (unfortunately no SQL Server Management Studio).
Answer
To change the database's collation
ALTER DATABASE MyDataBase COLLATE [NewCollation]
To change the collation of a column
ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation]
But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index.
You can do more in SSMS in some cases.
The syntax docs list the restrictions: