Menu
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).
328
You cannot change the collation of a column that is currently referenced by any one of the following: A computed column. An index. Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
January 27, 2020 by Jignesh Raiyani. SQL Server collation refers to a set of character and character encoding rules, and influences how information is stored according to the order in the data page, how data is matched by comparing two columns, and how information is arranged in the T-SQL query statement.
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.
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:
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
