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' GOTo 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 GOTo change the collation for a table or column reference the ALTER TABLE syntax.
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