Microsoft SQL Server Unicode

Question

Hi,

I have created a new table in Microsoft SQL on Azure but the fields are getting the following Collate:

SQL_Latin1_General_CP1_CI_AS

How can I change it to Unicode and also how can I ensure my default is Unicode for the new tables?

Thanks,

Jassim

Answer 1

Change an existing column to Unicode:

ALTER TABLE your_table_name
ALTER COLUMN your_column_name NVARCHAR(max_length);

Set the default collation for new tables to Unicode

ALTER DATABASE your_database_name
COLLATE Latin1_General_CI_AS;

Answer 2

How can I change it to Unicode

??? The collation has really something to do with Unicode, but with ASCII text.

If you want to store Unicode text, use the related datatypes NVarChar.

Collation take only effect on columns with data type VarChar = ASCII data.

See

https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16

Answer 3

Hi
Jassim Al Rahma •,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand, you want change data collation in SQL Server or Azure SQL MI.

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.

Changing the collation of a database or individual columns does not modify the underlying data already stored in existing tables. Unless your application explicitly handles data conversion and comparison between different collations, it is recommended that you transition existing data in the database to the new collation. This removes the risk that applications may incorrectly modify data, resulting in possible wrong results or silent data loss.

When a database collation is changed, only new tables will inherit the new database collation by default. There are several alternatives to convert existing data to the new collation:

• Convert data in-place. To convert the collation for a column in an existing table, see Set or Change the Column Collation. This operation is easy to implement, but may become a blocking issue for large tables and busy applications.
• Copy data to new tables that use the new collation, and replace original tables in the same database. Create a new table in the current database that will inherit the database collation, copy the data between the old table and the new table, drop the original table, and rename the new table to the name of the original table. This is a faster operation than an in-place conversion, but may become a challenge when handling complex schemas with dependencies such as Foreign Key constraints, Primary Key constraints, and Triggers. It would also require a final data synchronization between the original and the new table before the final cut-off, if data continues to be changed by applications.
• Copy data to a new database that uses the new collation, and replace the original database. Create a new database using the new collation, and transfer the data from the original database via tools like Integration Services or the Import/Export Wizard in SQL Server Management Studio. This is a simpler approach for complex schemas. It would also require a final data synchronization between the original and the new databases before the final cut-off, if data continues to be changed by applications.

To create a new database, requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

To change the collation of an existing database, requires ALTER permission on the database.

Note: The ALTER DATABASE COLLATE statement is not supported on Azure SQL Database.

Reference Link: Set or change the database collation

However, if you want to change collation in Azure SQL Database, please refer to the below article link:

Change collation of existing Azure SQL database

Hope this helps. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.