these language elements let you retrieve information such as the name and ID of the current language used, the maximum number of simultaneous user connections permitted or the version of the SQL Server instance which you’re connected to. 

Retrieving data through SQL Server configuration functions is straightforward. In most cases, you can create a simple SELECT statement  that calls the function you want to use. In this article, I describe many of the configuration functions available and provide examples that show you how they work. I created the examples on a local instance of SQL Server 2008, but they will also run on SQL Server 2005. Because SQL Server configurations can vary from instance to instance, however, the results I show here might not match those you receive on your system, but they should at least give you an idea of what to expect.

The first configuration function we’ll look at is @@DATEFIRST. In SQL Server, one of the configuration settings automatically assigned to a session is the first day of the calendar week. By default, that day is Sunday (though you can issue a SET statement to override the default). You can use the @@DATEFIRST function to return the first-day setting, as shown in the following statement:

SELECT @@DATEFIRST AS FirstDay;

Because I did not change the first-day setting on the instance of SQL Server I’m using, the statement will return Sunday. Note, however, that the function returns only the numerical equivalent of Sunday, which is 7. For that reason, I modified the SELECT statement to include a CASE expression that returns the name of the day, rather than its numerical equivalent, as shown in the following example:

SELECT

  CASE @@DATEFIRST

    WHEN 1 THEN 'Monday'

    WHEN 2 THEN 'Tuesday'

    WHEN 3 THEN 'Wednesday'

    WHEN 4 THEN 'Thursday'

    WHEN 5 THEN 'Friday'

    WHEN 6 THEN 'Saturday'

    WHEN 7 THEN 'Sunday'

  END AS FirstDay;

As you would expect, the statement now returns the name Sunday because the @@DATEFIRST function returns a value of 7.

The next configuration function we’ll look at is @@DBTS. The function returns the last used timestamp value in a specified database. For example, if you insert or update data in a table with a timestamp column, that column is updated with a current timestamp value. You can then use the @@DBTS function to retrieve the timestamp value, as I’ve done in the following example:

USE AdventureWorks2008;

SELECT @@DBTS AS DbTimeStamp;

Notice that I first specified the name of the database. That’s because timestamp values are specific to each database. You can specify any database for which you want to retrieve the most recent timestamp value. Next I ran the SELECT statement that includes the @@DBTS function.

The @@DBTS function returns the timestamp value as varbinary data, in this case, a value of 0x0000000000020791. Keep in mind that timestamp values have nothing to do with times or dates. They are meant only to show the sequence in which data was modified and originally implemented in order to support database recovery algorithms.

Another SQL Server setting that you can access by using configuration functions is the current language. To retrieve this information, use one of two functions: @@LANGID or @@LANGUAGE. The @@LANGID function returns the local language identifier, and the @@LANGUAGE function returns the language name. For example, the following SELECT statement returns both the language ID and name:

SELECT

  @@LANGID AS CurrentLangID,

  @@LANGUAGE AS CurrentLang;

As the following results show, the SELECT statement returns a value of 0 for the language ID and a value of us_english for the language name: 

CurrentLangID	CurrentLang
0	us_english

These are the values returned for a default installation of SQL Server.

Now let’s look at how to retrieve the maximum number of user connections permitted on an instance of SQL Server. To do this, use the @@MAX_CONNECTIONS function, as shown in the following example:

SELECT @@MAX_CONNECTIONS AS MaxConnections;

On my system, the SELECT statement returned 32,767. Note that the number of connections depends on your version of SQL Server and on the limitations of the hardware and applications.

The next function is @@MAX_PRECISION, which returns the precision level used by decimal and numeric data. By default, the precision is 38, which is the value returned by the following SELECT statement:

SELECT @@MAX_PRECISION AS MaxPrecision;

Now let’s look at the @@OPTIONS function. SQL Server supports a number of user options that can be configured by using the SET command. The options settings are saved as a binary value that indicates how those options have been configured. The @@OPTIONS function returns an integer that represents the binary value. For example, when I run the following SELECT statement, the @@OPTIONS function returns a value of 5496:

SELECT @@OPTIONS AS SetOptions;

Suppose I now change one of the user options. For instance, in the following statement, I set the NOCOUNT option to ON, and then use the @@OPTIONS function to return the new options value:       

SET NOCOUNT ON;

SELECT @@OPTIONS AS SetOptions;

Now the SELECT statement returns a value of 6008, which indicates that the binary value that stores the user settings has changed. However, if I run the following SET statement to set the NOCOUNT option to OFF and then call the @@OPTIONS function, the function again returns a value of 5496:

SET NOCOUNT OFF;

SELECT @@OPTIONS AS SetOptions;

You can also use configuration functions to return general information about an instance of SQL Server. In the following SELECT statement I retrieve the server name, service name, session ID and text size:

SELECT

  @@SERVERNAME AS ServerName,

  @@SERVICENAME AS ServiceName,

  @@SPID AS SessionID,

  @@TEXTSIZE AS TxtSize;

Let’s look at these functions individually:

• @@SERVERNAME: Returns the name of the instance of SQL Server you’re connected to. If it is a default instance, the name is the operating system server on which SQL Server is installed. If it is a named instance of SQL Server, the function returns the server name and instance name.
• @@SERVICENAME: Returns the name of the SQL Server service used for a particular instance. If it is the default instance of SQL Server, MSSQLSERVER is used. If the installation is a named instance, that name is returned.
• @@SPID: Returns the session ID of the current user process (formerly the “server process ID”).
• @@TEXTSIZE: Returns the current setting of the TEXTSIZE option in bytes. The option determines the size of varchar(max), nvarchar(max), varbinary(max), text, ntext and image data that a SELECT statement returns.

As you can see in the following results, the name of the current instance of SQL Server is SRV023\SQLSRV2008, the service name is SQLSRV2008, the session ID is 54, and the TEXTSIZE setting is 2,147,483,647 bytes:   

ServerName	ServiceName	SessionID	TxtSize
SRV023\SQLSRV2008	SQLSRV2008	54	2147483647

One other configuration function we’ll look at is @@VERSION, which returns the version, processor architecture, build date and operating system for the current instance of SQL Server:

SELECT @@VERSION AS InstanceVersion;

On my system, the SELECT statement returns the following results:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

That’s all there is to using SQL Server configuration functions. As you can see, you can simply call the function in a SELECT statement. And as you’ve probably also noticed, all the configuration functions are preceded with the double at symbol (@@), which makes them easy to identify. In this article, I’ve covered most of the configuration functions available in SQL Server, but not all of them. For a complete list, see the topic “Configuration Functions (Transact-SQL)” in SQL Server Books Online. Each function listed is linked to the full description of that function.