Setting & working with language in SQL SERVER

In this tutorial, learn how to work with different languages in SQL Server. Learn how to change SQL server language settings. Find out the language list that SQL server supports along with the language codes & language id etc. We also lean how to change the language settings in case of contained database.

Finding the Current Language

@@LANGUAGE global variable returns the name of the language that sql server uses currently.

Finding the language List

To find the list of languages that SQL Server supports run sp_helplanguage stored procedure without any parameter

It will list the langid (language id), name (language name) etc.

Language Settings in SQL Server

How you set the language of a database depends on whether it is a contained database or not.

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. This SQL Server introduced this feature since version 2012.

If the database is not contained then the settings needs to change at instance level and at login account.

In case of contained database, you need to set it for the database & database user account.

We look at the both the scenarios.

Setting Language for SQL Server / Instance

In SQL Server language can be set at three places.

1. Instance
2. User Login Account
3. Current Session

SQL Server always uses the language set in the current session. If it not set then it uses the default language from the users login account.

The Instance Level language settings are used as the default language when we create a new login account.

Instance Level Language Settings

The SQL Server picks up the language settings from the OS at the time of installation We can change this later either using the

1. SQL Server Enterprise Manager
2. TSQL Query

Change Default Language Using SSMS

1. In Object Explorer, right-click on the server instance and select Properties.
2. Click the Advanced Tab
3. Choose the language from the Default language drop down.
4. Click on Save

Change Default Language Using Query

1. Run exec sp_helplanguage to view the list of languages
2. Note down the language id of the language you want to set.
3. Run the sp_configure as shown below

Note that changing the settings at instance level will not affect the already existing login account. But it will make the new language as default for the new login accounts.

Setting Language Settings for Login account

To change the language of an existing login you can either use the GUI Tools in SSMS or execute a ALTER LOGIN query.

Using SSMS

1. Open the SSMS
2. Select the User under the Security-> Login option
3. Right click on click on properties
4. Under the General tab choose the desired language from Default Language from the drop down.
5. Click on Ok to save

Using Query

You can use the Alter User Query to change

Setting Language for Contained Database

The SQL Server since version 2012 has the option of creating contained database. In case of contained database the language is set at

1. Database level
2. Contained User Level
3. Session

Setting Language at Database Level

Using SSMS

1. In Object Explorer, right-click on the database and select Properties.
2. Click the Options Tab
3. Choose the language from the Default language drop down under the section containment. You will see this option only in case of contained database.
4. Click on Save

Using Query

• Run exec sp_helplanguage to view the list of languages
• Note down the language id, lcid or language name of the language you want to set.
• Run the ALTER DATABASE as shown below with either language id, lcid or language name

Setting Language of the User

Using SSMS

1. In Object Explorer, under the database -> Security -> Users, select the users.
2. Click the General Tab
3. Choose the language from the Default language drop down. This option is only if the database is a contained database.
4. Click on Save

Using Query

Run the ALTER USER as shown below with either language id, lcid or language name

Session Level Language Settings

Changing the session level language is same for both contained and non contained scenarios. Use the SET LANGUAGE TSQL Command to change the language of the current session.

This will only effect the current session.

The SET LANGUAGE also uses the SET DATEFORMAT to set the date format

Reference

• syslanguages
• sp_helplanguage (Transact-SQL)]