Q

Changing the language option, part 1

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

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?

Language

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

Dig deeper on Microsoft SQL Server Installation

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:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close