How to change default language for SQL Server?

Question

Now when I query

SELECT @@language 

it gets 'us_english'. But I need russian.

I can't use SET LANGUAGE russian for every query.

I need to set it by default (for all new sessions).

Answer 1

Using SQL Server Management Studio

To configure the default language option

1. In Object Explorer, right-click a server and select Properties.
2. Click the Misc server settings node.
3. In the Default language for users box, choose the language in which Microsoft SQL Server should display system messages. The default language is English.

Using Transact-SQL

To configure the default language option

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.

This example shows how to use sp_configure to configure the default language option to French

USE AdventureWorks2012 ; GO EXEC sp_configure 'default language', 2 ; GO RECONFIGURE ; GO 

• Configure the default language Server Configuration Option

The 33 languages of SQL Server

| LANGID |        ALIAS        | |--------|---------------------| |    0   | English             | |    1   | German              | |    2   | French              | |    3   | Japanese            | |    4   | Danish              | |    5   | Spanish             | |    6   | Italian             | |    7   | Dutch               | |    8   | Norwegian           | |    9   | Portuguese          | |   10   | Finnish             | |   11   | Swedish             | |   12   | Czech               | |   13   | Hungarian           | |   14   | Polish              | |   15   | Romanian            | |   16   | Croatian            | |   17   | Slovak              | |   18   | Slovenian           | |   19   | Greek               | |   20   | Bulgarian           | |   21   | Russian             | |   22   | Turkish             | |   23   | British English     | |   24   | Estonian            | |   25   | Latvian             | |   26   | Lithuanian          | |   27   | Brazilian           | |   28   | Traditional Chinese | |   29   | Korean              | |   30   | Simplified Chinese  | |   31   | Arabic              | |   32   | Thai                | |   33   | Bokmål              | 

Answer 2

@John Woo's accepted answer has some caveats which you should be aware of:

1. Default language setting of a session is controlled from default language setting of the user login instead which you have used to create the session. SQL Server instance level setting doesn't affect the default language of the session.
2. Changing default language setting at SQL Server instance level doesn't affects the default language setting of the existing SQL Server logins. It is meant to be inherited only by the new user logins that you create after changing the instance level setting.

So, there is an intermediate level between your SQL Server instance and the session which you can use to control the default language setting for session - login level.

SQL Server Instance level setting -> User login level setting -> Query Session level setting

This can help you in case you want to set default language of all new sessions belonging to some specific user only.

Simply change the default language setting of the target user login as per this link and you are all set. You can also do it from SQL Server Management Studio (SSMS) UI. Below you can see the default language setting in properties window of sa user in SQL Server:

Note: Also, it is important to know that changing the setting doesn't affect the default language of already active sessions from that user login. It will affect only the new sessions created after changing the setting.