Ask the Expert

Changing the language option, part 1

How do you change the language option in SQL Server 2000?

    Requires Free Membership to View

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
SELECT DB_NAME() AS 'Database Name'
SELECT @@LANGUAGE AS 'Language Currently in Use'
Sort order and character set
USE User_Database
SELECT DB_NAME() AS 'Database Name'
Enterprise 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

How to rebuild the master database (Rebuild Master utility).

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 ]

< filespec > ::= 

( [ 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 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

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: