In what way can I set a collation of a column with SQL?

Question

Recently I created my SQL Server database on my local computer. I have set its collation to Latin1_General_CI_AI and it is working really fine. I got a problem when I moved the completed work to the web hosting SQL Server. The problem is: they use a different database collation. What should be done here?

More precisely, I need Latin1_General_CI_AI, but they have Czech_CI_AS. There is a difference between them when comparing strings in the Czech language. 

The moment I tried to change the collation of my database, the server asked for the user's permission to do that. Please guide me with this. 

Answer 1

Try this query if you want to change the database's collation:

ALTER DATABASE MyDataBase COLLATE [NewCollation]

And if you want to change the collation of a column:

ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation]

But there are many limitations on when this can be performed, but this can be denied if the column is used in any index.

If you want to get more insights into SQL, check out this SQL Course from Intellipaat.