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 T-SQL session in SQL Server Management Studio(SSMS) is inherited/overriden from/by Default language setting of the user login used to initiate the session instead. A new tab in SSMS creates a new T-SQL session. SQL Server instance level setting does not control the Default language setting of T-SQL session directly.
2. Changing Default language setting at SQL Server instance level has no effect on the Default language setting of the already existing SQL Server logins. It is meant to be inherited only by the new user logins that we create after changing the instance level setting. So don't be surprised if you changed the Default language setting at SQL Server instance level but it didn't take effect for your user account. It is as per design.

So, there is an intermediate configuration level between SQL Server instance level and the T-SQL session level. It is called user login level. You can use this intermediate level configuration to control the Default language setting for T-SQL session without disrupting the SQL Server instance level settings.

    SQL Server Instance level setting
                 |
                 V
    
    User login level setting
                 |
                 V
    
    T-SQL Query Session level setting

This intermediate level setting is very helpful in case you want to set Default language setting to some value for all new T-SQL sessions(tabs in SSMS) belonging to some specific user.

We can change the Default language setting of the target user login as per this link. You can also achieve it from SSMS console e.g. we can change the Default language setting from the properties window of sa user in SQL Server via SSMS (Refer screenshot):

Note: Changing the setting at user login level will not affect the setting of already active tabs in SSMS. It will affect only the new tabs which will be opened after changing the setting.

Answer 3

Please try below:

DECLARE @Today DATETIME;  
SET @Today = '12/5/2007';  

SET LANGUAGE Italian;  
SELECT DATENAME(month, @Today) AS 'Month Name';  

SET LANGUAGE us_english;  
SELECT DATENAME(month, @Today) AS 'Month Name' ;  
GO  

Reference:

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-language-transact-sql

Answer 4

If you want to change MSSQL server language, you can use the following QUERY:

EXEC sp_configure 'default language', 'British English';