Basically, collation defines the rules for how strings of characters data are stored and compared. Based on the norms of a particular language it will vary. Query conditions will change depending on the language configured. The server collation acts as the default collation for all the system databases and also newly created databases. we can specify the required collation during installation otherwise it will take the default one. Collation can be changed after installation using some commands. When we change the collation after installation then it will rebuild the databases means, it will recreate the databases and its index.
Steps to the change collation after installation
- Before changing, collation it\’s better to take databases backups.
- To check current or installed collation go to Server properties and find server collation or run this script in master DB
SELECT SERVERPROPERTY(N\'Collation\') - Stop the SQL Server Services from Control Panel–> Administrative Tools–> services
- Open a command prompt window with Run as Administrator and navigate to SQL Server BINN directory. Generally, it is there in the installation folder(C:\\Program Files\\Microsoft SQL Server\\MSSQLXX.MSSQLSERVER\\MSSQL\\Binn>).
- Then execute the script
sqlservr -m -Txxxx -s\"SqlServer\" -q\" SQL_Latin1_General_CP850_CI_AI \"
[-m] Single user admin mode
[-T] trace flag turned on at startup (check Microsoft for more info if used -T )
[-s] SQL Server instance name
[-q] new collation name to be applied
For more info on Sqlservr parameters, Microsoft Docs.
all existing databases and its index will be rebuilt and will get a final message like \”Recovery is complete\” - Restart the SQL Server service and check the changed collation.
/Siva