Problem
There are a lot of configuration settings in SQL Server which we update based as needed. SQL Server provides many ways to check these configuration settings. One such way to check these configuration settings is by using configuration functions. Today, I will discuss SQL Server configuration functions and some use cases to understand how we can use these to check configuration settings.
Solution
The SQL Server configuration functions return the existing value of the configuration option setting. These functions do not always return the same result by executing them with the same inputs and because of this behavior, these functions operate in a non-deterministic way. SQL Server offers various configuration functions to fetch distinct configuration option settings. Find the below list of all configuration functions offered in SQL Server.
- @@DBTS
- @@LANGID
- @@LANGUAGE
- @@DATEFIRST
- @@SPID
- @@VERSION
- @@SERVERNAME
- @@LOCK_TIMEOUT
- @@MAX_CONNECTIONS
- @@MAX_PRECISION
- @@NESTLEVEL
- @@OPTIONS
- @@REMSERVER
- @@SERVICENAME
- @@TEXTSIZE
You might already be using some of these configuration functions like @@VERSION, @@SERVERNAME, or @@SPID, etc. Today I will explain the use cases of some of these configuration functions.
@@DBTS
This configuration function returns the latest value of the TIMESTAMP data type for the current database. The TIMESTAMP is a data type that is used to automatically generate a unique time stamp value for the database. If you choose this data type for a column in a table then every time you do an INSERT or UPDATE this column will capture the time stamp and save it for future reference.
The SQL Server configuration function @@DBTS is used to return the latest TIMESTAMP value of a database. We can run the below statement to get its output for the database.
USE TESTDB
GO
SELECT @@DBTS
GO
Once I have executed the above T-SQL statement, its output has been returned as shown in the below image.
This value keeps on changing incrementally based on database activities. Let me show you this by creating a table and then inserting a few rows into that table finally will check this value again to ensure whether this value is changing or not. I executed the below statements to create a table named DBTS in the TESTDB database then I inserted a few rows and then checked the timestamp, we can see the timestamp is incrementally increasing.
CREATE TABLE DBTS (city varchar(15), timestamp timestamp )
GO
SELECT @@DBTS
GO
INSERT INTO DBTS(city) values('Gurgaon')
GO
SELECT @@DBTS
GO
INSERT INTO DBTS(city) values('Mumbai')
GO
SELECT @@DBTS
GO
I executed the above statements and got the below output. You can see after every insert statement, the timestamp is also changing in incremental order.
Now, I will show you the timestamp value using this configuration function after updating these records. I have executed the below T-SQL statement to update the city to Delhi and finally to Ballia. I had inserted 2 records in the above example, here each UPDATE statement will update both records with the same value. First, both records for the city will be updated to Delhi and then after checking the timestamp, it will again update as Ballia in the second UPDATE statement.
USE TESTDB
GO
UPDATE [dbo].[DBTS] SET [city] = 'Delhi'
GO
SELECT @@DBTS
GO
UPDATE [dbo].[DBTS] SET [city] = 'Ballia'
GO
SELECT @@DBTS
GO
Here, we can see the timestamp returned in the below screenshot.
When the first UPDATE statement executes, it will update the two records as Delhi, so the timestamps will change to **4 and **5.
When the second UPDATE statement runs it will again update the two records, so the timestamps will change to **6 and **7.
@@LANGID and @@LANGUAGE
Here, I will explain 2 more configuration functions using which you can get the current language being used for the SQL Server instance.
- @@LANGID which will return language identifies (language id) that is currently set. You can get a list of all languages and their language ids by running the sp_helplanguage system stored procedure.
- @@LANGUAGE will return the name of the language that is currently being used
I ran the system stored procedure sp_helplanguage to list all languages and their ids and then I executed @@LANGID and @@LANGUAGE to see the current language being used for the SQL Server instance.
sp_helplanguage
GO
SELECT @@LANGID AS [Language ID],
@@LANGUAGE AS [Language Name]
GO
Here you can see a list of all languages in the first result set and then the current language being used in the second result set.
Let’s change the language setting using the SET LANGUAGE statement and then see the result set returned by these configuration functions. I change the language to British English.
sp_helplanguage 'British English'
GO
SET LANGUAGE 'British English'
GO
SELECT @@LANGID AS [Language ID],
@@LANGUAGE AS [Langugae Name]
GO
Here is the output.
Here is another example.
@@DATEFIRST
The configuration function @@DATEFIRST is used to return the current configuration of the SET DATEFIRST statement. The SET DATEFIRST statement is used to set a specific day using numbers 1 to 7 as the first day of the week.
Each day is assigned a number as shown in the below table. The default value is 7 which is Sunday as the first day of the week. If you want to change this value to another day then you can do so by running SET DATEFIRST.
| Number | Day |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 | Sunday |
Let’s check the current configuration setting.
SELECT @@DATEFIRST AS [1st day of the Week]
You can see the output is 7 which means Sunday is set as the first day of the week which is also the default value.
Let’s change this value using the SET DATEFIRST statement and make Wednesday the first day of the week. We need to pass the assigned number for Wednesday from the above table in the SET DATEFIRST statement.
SELECT @@DATEFIRST AS [1st day of the Week]
GO
SET DATEFIRST 3
GO
SELECT @@DATEFIRST AS [1st day of the Week]
Here is the output.
You should be aware that the first day of the week configuration might be changed if you will change the language of the SQL Server. Let me show you an example.
The below statement will return the language being used along with the 1st day of the week configured for SQL Server.
SELECT @@LANGID AS [Language ID],
@@LANGUAGE AS [Language Name],
@@DATEFIRST AS [1st day of the Week]
We can see the details below.
Now, I will change the language to Swedish using the SET LANGUAGE statement and then run the same statements again.
SET LANGUAGE 'Swedish'
GO
SELECT @@LANGID AS [Language ID],
@@LANGUAGE AS [Language Name],
@@DATEFIRST AS [1st day of the Week]
GO
sp_helplanguage 'Swedish'
Below we can see that by default the first day of the week is set to 1 when we change the language to Swedish.
@@SPID and @@SERVERNAME
These are probably the most popular functions.
@@SPID is used to fetch the session id of the current connection or user process. @@SERVERNAME returns the name of the SQL Server instance.
The session id is the unique number that is assigned to each database connection. We can use this function using the below T-SQL statement to get the session id of the user connection.
SELECT @@SPID AS [Session ID]
I have executed the above statement and it has returned session id 70 for my current connection.
We can also use this configuration function along with other functions.
SELECT @@SPID AS [Session ID],
@@SERVERNAME AS [DB Server Name],
SYSTEM_USER AS [Login Name]
You can see the details in the below image that returns the session id and the name of the SQL Server instance.
@@VERSION
This returns the currently installed version of SQL Server.
SELECT @@VERSION AS [SQL Server Version]
Here is the output that shows the SQL Server version and also the operating system version information.
Below is another example where we can use multiple functions to return information about the server name and version of SQL Server.
SELECT @@SERVERNAME AS [DB Server Name],
@@VERSION AS [SQL Server Version],
SERVERPROPERTY('PRODUCTVERSION')
Next Steps
- Today I explained SQL Server configuration functions and demonstrated use cases of some of the popular configuration functions. You should start using these if you are not already. Leave a comment below if you would like to learn more about the other configuration functions.
