Q

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'
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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close