Today I want to talk about some of the language settings in SQL Server and the impact they have. Read on to see about setting up a default language in SQL Server, setting language for a scoped session, and differences between SQL Server and Windows Server settings.

Configuring Default Language in SQL Server

Language in SQL Server is an instance level configuration. Microsoft has some documentation about this: Configure the default language Server Configuration Option

A server restart is NOT required after changing this configuration.

Executing sp_helplanguage will show general info about languages.

--Call for ALL languages
exec sp_helplanguage;

--Call for Russian info
exec sp_helplanguage russian;

--Call for ALL languages

exec sp_helplanguage;

--Call for Russian info

exec sp_helplanguage russian;

Change Language in SSMS

Directions from SSMS: Connect to instance –> Right click instance in Object Browser –> Properties –> Advanced tab –> Default Language

NOTE: The Microsoft documentation is slightly off as it suggests to change under the general tab and not the advanced tab.

Changing it to Italian we can see the default language change via:

--default language
select * from sys.configurations where [name] like '%language%';

--lookup in syslanguages
select * from sys.syslanguages;

--combine them
select * 
from sys.configurations c inner join sys.syslanguages lang on c.[value] = lang.langid
where c.[name] like '%language%';

--default language

select * from sys.configurations where [name] like '%language%';

--lookup in syslanguages

select * from sys.syslanguages;

--combine them

select *

from sys.configurations c inner join sys.syslanguages lang on c.[value] = lang.langid

where c.[name] like '%language%';

Selecting from sys.syslanguages returns info about each language available in the SQL instance.

Change Language in T-SQL

Simple open a thread to the instance and, from any database, execute:

EXEC sp_configure 'default language', 2; --change default to French
GO
RECONFIGURE;
GO

EXEC sp_configure 'default language', 2; --change default to French

RECONFIGURE;

NOTE: changing the default language in SQL Server will NOT affect the scope of your query threads (i.e. tabs in SSMS).

Changing the default language will be marked in the SQL Server error log as such:

Setting a Session Scoped Language in SQL Server

Here is how to see the current language for a session via @@LANGUAGE:

--Returns the name of the language currently being used
select @@language;

1 2	--Returns the name of the language currently being used select @@language;

The SET LANGUAGE command allows us to choose a language for a session. By session here I mean by SPID. Each query tab you open in SSMS is another thread to the database and receives a SPID. This can be called by almost anyone who has permissions to access the database because it only requires membership in the public role to execute.

Now let us change the session language to Russian.

SET LANGUAGE Russian;

1	SET LANGUAGE Russian;

The message post execution is:

I think it means the language settings are now Russian. We can verify the change by querying:

select @@LANGUAGE;

1	select @@LANGUAGE;

From this point out all of the messages that SQL Server returns to you in the query message or errors will be Russian.

Example:

select 1/0; --dividing by zero to throw an error

1	select 1/0; --dividing by zero to throw an error

Here is another demonstration with dates:

DECLARE @Today DATETIME;
SET @Today = getdate();

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

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

DECLARE @Today DATETIME;

SET @Today = getdate();

SET LANGUAGE Russian;

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

SET LANGUAGE us_english;

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

My Russian is not that good so let’s change it back. Simply set the language as demonstrated above back to English and all is well again.

OS Language is Not the Same Thing as SQL Server Language

One last thing I want to mention is that the language setting in SQL Server are not the same as the OS i.e. Windows Server.

If the OS keyboard is set to Russian then it will use Russian characters when you type. The error messages, log entries, and everything else will still be English. It affects only your typing. Go ahead and change the language, open notepad or SSMS, and start typing. The letters you hit will not be what you expect.

If you go through the Control Panel and change the language then everything system wide will be in that language. Reboot the machine and the language will be changed.