When Microsoft released SQL Server 2000, it was delivered with a new set of functionality as compared to earlier releases, whereby changes related to data settings can be configured at a more granular level. These configurations are called the collation settings including the character set, sort order and locale-specific settings comprising the overall SQL Server language settings. SQL Server 2000 permits individual collation settings at the SQL Server, database and column levels which can be configured by DBAs. Although this concept is very simplistic, a few questions come to mind.
Question 1 -- How do I figure out which language, character set and sort order are in use from a T-SQL and Enterprise Manager perspective?
USE User_Database GO SELECT @@SERVERNAME AS 'Server Name' GO SELECT DB_NAME() AS 'Database Name' GO SELECT @@LANGUAGE AS 'Language Currently in Use' GOSort order and character set
USE User_Database GO SELECT @@SERVERNAME AS 'Server Name GO SELECT DB_NAME() AS 'Database Name' GO sp_helpsort GOEnterprise Manager
Figure 1 – SQL Server properties general tab displaying the language setting
Question 2 -- What are typical reasons to change these collation settings from the default?
- Expansion to international business
- Server consolidation from international locations
- Your organization needs to support products and services in multiple countries with different languages
Question 3 -- How can I change the collation for SQL Server?
If you need to change your entire SQL Server from one collation setting to another, be prepared for some planning, testing and implementation time because it will be necessary to rebuild the master database and all subsequent databases. Unfortunately, this is not one fast and simple task, but you do have the option to consider as opposed to just changing the collation settings at the database- or column-level as follows:
Rebuild the master database
Create a new database via Enterprise Manager
Figure 2 – Database creation with collation options
Create a new database via T-SQL code
CREATE DATABASE database_name [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON } < filespec > [ ,...n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ] < filespec > ::= [ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = 'os_file_name' [ , SIZE = size ] [ , MAXSIZE = } max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ] < filegroup > ::= FILEGROUP filegroup_name < filespec > [ ,...n ]Configure a column with a different collation setting
ALTER TABLE table } [ ALTER COLUMN column_name } new_data_type [ ( precision [ , scale ] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] | }ADD | DROP } ROWGUIDCOL } ]
This response is continued...
This was first published in February 2004