Ask the Expert

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?

    Requires Free Membership to View

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'
GO
To change the database collation for a single user database can be accomplished with the following command:
USE Tempdb
GO
ALTER DATABASE Pubs COLLATE French_CI_AS
GO
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.

This was first published in August 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: