Problem solve Get help with specific problems with your technologies, process and projects.

Changing collation name of specific database

How can I change the collation name of a specific database? The rebuildm.exe is changing the master database, but I want to change only a specific database. Is there a way to change it with SQL statements like ALTER? I developed an application with collation of Hebrew/Arabic, and now I wish to change the collation to English. How can I do it?

Database collation is the configuration for physically storing data in a SQL Server database. In SQL Server 2000 the collation can be configured at the SQL Server, database, table and column levels. During the SQL Server installation process, the collation is established and all SQL Server objects (i.e. databases, tables, columns, etc.) use the same collation configuration. Once SQL Server is installed then the SQL Server objects can be created with a different collation or the collation can be changed on existing objects. SQL Server's flexibility provides organizations with the ability to have customized collations on a single SQL Server to support users across the world at a column level as well as to simplify server consolidation projects manage multiple instances on a single SQL Server instance.

To determine the collation for a database issue the following command:

sp_helpdb 'pubs'
To change the database collation for a single user database can be accomplished with the following command:
USE Tempdb
To change the collation for a table or column reference the ALTER TABLE syntax.

For a listing of all of the SQL Server collation options reference the SQL collation name and Windows collation names.

In reference to the default SQL Server collation, I believe the answer depends on the Windows options, if the server is upgraded, etc., so I am not certain I can provide you an absolute answer. What I would recommend is if your SQL Server is already installed then reference the sp_helpdb syntax above. Then change the collation among the SQL Servers or the database objects to ensure consistency if the user requirements are equal. If not, then it is necessary to maintain differing collations to support business needs. With this being said, note that with differing collations result sets may differ. As such, verify the collation prior to any other troubleshooting steps or procedures relative to the result set.

For news, advice and other information about SQL server development, click here.

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.