To modify the collation of a SQL Server instance, database, table, field

Transferred from: http://blog.51cto.com/jimshu/1095780

Concepts and details please refer to: Character encoding and sorting rules: https://www.cnblogs.com/gered/p/9145123.html

I. Modifying the collation of a SQL Server server (instance)

The following experiment uses the default instance of SQL Server R2 to modify the chinese_prc_ci_as to SQL_Latin1_General_CP1_CI_AS.

1. Stop the SQL Server instance service

2. Open "Command Prompt" to go to the installation directory of SQL Server

c:\users\administrator> cd "\program Files\Microsoft SQL Server\100\setup Bootstrap\sqlserver2008r2"

3. Run Setup

C:\Program Files\Microsoft SQL Server\100\setup bootstrap\sqlserver2008r2> Setup/quiet/action=rebuilddatabase/ Instancename=mssqlserver/sqlsysadminaccounts=administrator/sqlcollation=sql_latin1_general_cp1_ci_as

4. Start the SQL Server instance service

5. Verifying The collation of the instance

6. Verifying The collation of the system database (master, model, msdb, tempdb)

Description: This action affects only the system database. Invalid for a user database that already exists.

Second, modify the collation of the database

The following experiment modifies the collation of the database db01 and modifies the sql_latin1_general_cp1_ci_as to chinese_prc_ci_as.

1. SSMs Graphical interface

2. Scripts

ALTER DATABASE [DB01] COLLATE chinese_prc_ci_as

3. Limitations

(1) For data that already exists, this operation does not cause immediate reordering.

(2) If an object is already dependent on the database collation, the change is unsuccessful.

Third, modify the collation of the table

The collation of the table depends on the collation of the database. Cannot be modified.

Iv. Modifying the collation of a column

Recommendation: Save this table's data to another temporary table, rebuild the table, and then import the data from the staging table to the new table.

You can modify the collation of a column through SSMs, but the operation actually deletes the table and associated constraints, triggers, and then rebuilds the table, constraints, indexes, triggers.

ALTER TABLE dbo. Empbasic DROP CONSTRAINT df__empbasic__firstn__2319cd4b ALTER TABLE dbo. Empbasic DROP CONSTRAINT ... CREATE TABLE dbo. Tmp_empbasic (...) FirstName nvarchar (COLLATE) chinese_prc_ci_as NULL, ...... ) On [PRIMARY] ALTER TABLE dbo. Tmp_empbasic SET (lock_escalation = TABLE) GRANT DELETE on dbo. Tmp_empbasic to public as dbo GRANT INSERT on dbo. Tmp_empbasic to public as dbo GRANT SELECT on dbo. Tmp_empbasic to public as dbo GRANT UPDATE on dbo. Tmp_empbasic to public as dbo ALTER TABLE dbo. Tmp_empbasic ADD CONSTRAINT df__empbasic__firstn__2319cd4b DEFAULT (") for FirstName ALTER TABLE dbo. Tmp_empbasic ADD CONSTRAINT df__empbasic__middle__240df184 ... SET IDENTITY_INSERT dbo. Tmp_empbasic on IF EXISTS (SELECT * FROM dbo. Empbasic) EXEC (' INSERT into dbo '. Tmp_empbasic (company, EmpID, FirstName,......) SELECT Company, EmpID, FirstName, ... FROM dbo. Empbasic with (HOLDLOCK Tablockx) ') SET IDENTITY_INSERT dbo. Tmp_empbasic OFF DROP TABLE dbo. Empbasic EXECUTE sp_rename N ' dbo. Tmp_empbasic ', N ' empbasic ', ' OBJECT ' CREATE UNIQUE CLUSTERED INDEX EmpID on dbo. Empbasic (company, EmpID) With (Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = ON, Allow_page_locks = OFF) on [PRIMARY] CREATE nonclustered INDEX FirstName on dbo. Empbasic (company, FirstName, LastName) With (Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = ON, Allow_page_locks = OFF) on [PRIMARY] Create Trigger _ti_empbasic on dbo. Empbasic for Insert as Begin ...... End

To modify the collation of a SQL Server instance, database, table, field