Change the Default Collation of SQL server, Database 

I had installed a SQL server with default collation SQL_Latin1_General_CP1_CI_AS but later realized it should be SQL_Latin1_General_CP850_CI_AS. I followed the below steps. 

1. Change SQL Server (Instance) Collation

⚠️ You cannot change the server collation without rebuilding the system databases, which means reinstalling SQL Server components.

Steps:

• Backup all user databases and settings

• Use setup.exe from installation media with REBUILDDATABASE flag

Example Command:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=domain\user /SAPWD=StrongPassword /SQLCOLLATION=New_Collation_Name

🔁 Example collation: SQL_Latin1_General_CP1_CI_AS

🧷 This will remove all system data, including logins and jobs. Use with caution.

INSTANCENAME= MSSQLSERVER for Default Instance

              ServerName\Instance for named instance

SQLSYSADMINACCOUNTS = Must provide windows account

SAPWD = Its going to be new password for 'SA' account.

SQLCOLLATION = New collation to be changed

2. Change Database Collation

You can change a database's default collation only if no tables exist, or you'll have to recreate all objects manually.

Example (for empty or new DB):

ALTER DATABASE YourDatabaseName COLLATE New_Collation_Name;


3. Change Column Collation
To change collation for specific columns:
ALTER TABLE YourTable
ALTER COLUMN YourColumn VARCHAR(100) COLLATE New_Collation_Name;
⚠️ This can fail if the column is part of an index or constraint.

4. Specify Collation in Queries
For joining or comparing across different collations:

SELECT * FROM TableA JOIN TableB ON TableA.Name 
COLLATE Latin1_General_CI_AS = TableB.Name COLLATE Latin1_General_CI_AS;