Changing collation name of specific database

RELATED CONTENT Archive: Backup and Recovery Synchronize databases on two separate active servers Back up tables from SQL Server 2005 database to a file Restore SQL Server 2000 backups in SQL Server 2005 Restoring a SQL Server database with a corrupt data file SQL Server error message: "Server out of memory" Configure log shipping between SQL Server 2000 and 2005 Restoring from msdb with only .mdf and .ldf files Extended stored procedure to back up databases Change tempdb from 'C' drive Back up stored procedure only

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

EXPERT RESPONSE FROM: Greg Robidoux Pose a Question Other SQL Server Categories Meet all SQL Server Experts Become an Expert for this site

ANSWERED August 2004:

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

USE Tempdb
GO
ALTER DATABASE Pubs COLLATE French_CI_AS
GO

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.