The example creates the database MyOptionsTest that uses the Latin1_General_100_CS_AS_SC collation. This example shows how to use the COLLATE clause in CREATE DATABASE to specify a collation name. Set the database collation using Transact-SQLĬopy and paste the following example into the query window and select Execute. Select the Options page, and select a collation from the Collation drop-down list. If you don't want the default collation, select the Options page, and select a collation from the Collation drop-down list.Īlternatively, if the database already exists, right-click the database that you want and select Properties. If you are creating a new database, right-click Databases and then select New Database. In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Set or change the database collation using SSMS To change the collation of an existing database, requires ALTER permission on the database. To create a new database, requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission. 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. This is a simpler approach for complex schemas. 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. See the following example for a "copy and replace" conversion of the MyString column to a new collation: CREATE TABLE dbo.MyTable2 (MyString VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8) ĮXEC sp_rename 'dbo.MyTable2', 'dbo.MyTable' Ĭopy data to a new database that uses the new collation, and replace the original database. 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. 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. 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. See the following example for an in-place conversion of the MyString column to a new collation: ALTER TABLE dbo.MyTableĪLTER COLUMN MyString VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8 Ĭopy data to new tables that use the new collation, and replace original tables in the same database. This operation is easy to implement, but may become a blocking issue for large tables and busy applications. To convert the collation for a column in an existing table, see Set or Change the Column Collation. There are several alternatives to convert existing data to the new collation:Ĭonvert data in-place. When a database collation is changed, only new tables will inherit the new database collation by default. This removes the risk that applications may incorrectly modify data, resulting in possible wrong results or silent data loss. 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. These can be changed by using the COLLATE clause of ALTER TABLE.Ĭhanging the collation of a database or individual columns does not modify the underlying data already stored in existing tables. This statement does not change the collation of the columns in any existing user-defined tables. 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. The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, are changed to the new default collation. When you change the database collation, you change:Īny char, varchar, text, nchar, nvarchar, or ntext columns in system tables are changed to the new collation.Īll existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions are changed to the new collation. You can find the supported collation names in Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL) or you can use the sys.fn_helpcollations (Transact-SQL) system function. The ALTER DATABASE COLLATE statement is not supported on Azure SQL Database.
0 Comments
Leave a Reply. |