SQL Server change collation of database

A SQL Server collation setting governs the code page to store non Unicode data in SQL Server and the rules to sort and compare non Unicode characters.  The SQL Server installation automatically selects the collation depending on the windows collation on which it is being installed. If required here is how to change collation of a database.

Database collation information can be retrieved via T-SQL and GUI as shown below.

1_sql server change collation of database

The function DATABASEPROPERTYEX can be used to get database collation as shown above. Additionally the collation details can also be obtained from sys.databases.

The collation settings can also be retrieved by right clicking database in SSMS object explorer and checking its properties as shown below.


2_sql server change collation of database

The SQL Server database can also be changed by changing the Collation property from database property dialog box under the options pane, by selecting a value from the drop down list ( highlighted in green) as shown in above image.

Here is a T-SQL to change database collation.

3_sql server change collation of database

The T-SQL ALTER DATABASE terminates with error if there are object which depend on database collation.


Like us on FaceBook Join the fastest growing SQL Server group on FaceBook


Leave a Reply

Your email address will not be published.