Six Useful T-SQL Configuration Functions and Their Use Cases

SQL Server offers several types of system functions to meet our business requirements. One such type is configuration functions using which we can get the information about current configuration option settings. These functions operate in a non-deterministic way, which means they may return different values by running these functions with the same inputs. 

Today I will showcase the use cases of the below configuration functions in this article.

• @@SERVERNAME

• @@SERVICENAME

• @@LOCK_TIMEOUT

• @@MAX_CONNECTION

• @@TEXTSIZE

• @@LANGID

Use Cases 

Here, I will demonstrate the use cases of each configuration option that is listed in the above section. You can use SQL Server management studio to connect to your SQL Server instance and practice these use cases there, or you can subscribe to feature-rich dbForge SQL tools for your development work or practice the below use cases. Let’s start with one of the popular functions @@SERVERNAME in the below section.

@@SERVERNAME

This function returns the name of the local server on which the SQL Server is running. Let’s run this function to understand its output.

SELECT @@SERVERNAME AS [DB Server Name]

Once I had executed the above statement, I got the name of my database server, as shown in the below image. If you are running the default instance of SQL Server, then the output will return as the local server name on which SQL Server is running, but if you are running multiple SQL Server instances on the system, then the output will return as ‘SERVERNAME\INSTANCE’ for named instances.

The output of this function is quite similar to the output of the SERVERNAME property of the SERVERPROPERTY function. I have executed the below statements in which I have used function @@SERVERNAME and SERVERPROPERTY statement to return the name of the local server name on which SQL Server is running.

SELECT @@SERVERNAME AS [DB Server Name],
    SERVERPROPERTY(‘SERVERNAME’] 

The output is showing as the same for both statements in the below image.

@@SERVICENAME

The next configuration function is @@SERVICENAME which is used to return the name of the registry key under which SQL Server is running. If you have a default instance installed, then this function will return “MSSQLSERVER”, but if you are running this function on a named instance, then it will return the instance name of the SQL Server as its output. Suppose you have a named instance DBSERVER\SQLINSTANCE then the output of this function will be “SQLINSTANCE”. If you observe, then you will find that the same names will also be there in SQL Server service names.

I have executed the below T-SQL statements to get the registry key under which my SQL Server instance is running. This is also the name of the SQL Server instance or SQL Server service name.

SELECT @@SERVICENAME AS [SQL Instance Name]

As I am running this statement on the default instance so you can see the default instance name in the output.

We can also use this function along with function @@SERVERNAME to return the database server name and instance name. We need such information many times for various reasons. We can use these functions to list out all database servers and their instance names easily. Let me show you a simple example to get this information for my SQL Server.

SELECT @@SERVERNAME AS [DB Server Name],
@@SERVICENAME AS [SQL Instance Name]

I have used both functions in the above statement and executed them to get our desired output in which we can see the database server name and SQL instance name both.

@@LOCK_TIMEOUT

This function returns the current lock timeout setting in milliseconds for the current session. We can change the lock timeout settings using the SET LOCK_TIMEOUT statement for the current session. The lock timeout setting allows the session to wait until the configured value is on a blocked resource. If the wait time exceeds the value, then the session will be canceled. 

Function @@LOCK_TIMEOUT will return -1 if the SET LOCK_TIMEOUT statement has not been executed for the current session. Let’s run this function to check the current value of lock timeout for my current session by running the below statements.

SELECT @@LOCK_TIMEOUT AS [Lock Timeout]
GO

I have not used SET LOCK_TIMEOUT yet for my current session, so the output is showing as -1.

Next, I will configure lock timeout using the SET LOCK_TIMEOUT statement and then will execute the @@LOCK_TIMEOUT function to check the output. Run the below statements to set the lock timeout and then return the output for this function.

SET LOCK_TIMEOUT 3000
GO

SELECT @@LOCK_TIMEOUT AS [Lock Timeout]
GO

Now, the output of the above statement is showing as 3000 because we already have its value in the first statement. 

@@MAX_CONNECTION

This function returns the maximum number of simultaneous connections SQL Server can allow. Remember, this is not the current configured number of connections to SQL Server. The maximum number of allowed connections can vary for different versions of SQL Servers.

We can run the below statement to get the maximum number of simultaneous connections allowed on the SQL Server instance.

SELECT @@MAX_CONNECTIONS AS [Max Connection Count]
GO

The output is showing as 32767, which is the maximum number of connection counts on SQL Server.

If you want to limit the number of simultaneous connections, then you can use the sp_configure stored procedure to configure the acceptable number of connections. I have executed the above statement again along with sp_configure in the below screenshot. The maximum user options allowed using sp_configure are also showing the same as the output returned by the function @@MAX_CONNECTION in the below screenshot.

@@TEXTSIZE

The next configuration function is @@TEXTSIZE. This function returns the current value of the TEXTSIZE option. TEXTSIZE is used to specify the size, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned to the client by a SELECT statement. The maximum size can be set as 2147483647, which is 2 GB. 

Let’s check the current text size by running the below statement.

SELECT @@TEXTSIZE AS [Current TEXTSIZE value]
Go

The output of the above statement is returning as the maximum value, which can be set for the TEXTSIZE option.

Next, I will change the above value by using the SET TEXTSIZE statement and check the newly configured value using this function @@TEXTSIZE. Run the below T-SQL statement to set its new value and then again check the newly configured text size value.

SET TEXTSIZE 100000
GO
SELECT @@TEXTSIZE AS [Current TEXTSIZE value]
Go

Now, the output of the above statements is showing as the newly configured value of the TEXTSIZE option in the below screenshot.

@@LANGID

We can also check the current language being used on SQL Sever using a configuration function @@LANGID. This function will return the language id of the current configured value of the SET LANGUAGE statement. If you want to understand the name of the language, then you can either use another configuration function @@LANGUAGE or you can compare the language id returned by @@LANGID with the output of a system stored procedure sp_helplanguage. This stored procedure list all the languages and their other details. I have executed this stored procedure in the below screenshot shown along with this function @@LANGID.

Here, we can see the output returned by function @@LANGID is 0, which means it is us_english, which can be seen in the first row of the output of sp_helplanguage system stored procedure.

Function @@LANGID will always return the latest configured language, so if you change the language using the SET LANGUAGE statement, then function @@LANGID will return the newly configured language in its output.

I have explained the use cases of some of the SQL Server configuration functions in this article. SQL Server configuration function returns the information about current configuration settings, and I have shown this by using various examples in the above sections. You can use them in your business requirements as per your need.