SQL Configuration Functions - SQL Programmers

SQL Configuration Functions

SQL Server provides many built-in functions and also lets you create user-defined functions. Configuration Functions are one of the built-in functions that help the user access information about the current configuration.

All Configuration Functions are nondeterministic. These functions do not always return the same results each time they are called, even with the same set of input values.  This article explains each Configuration Function one by one and provides examples.
@@DATEFIRST

@@DATEFIRST helps the user access the first day of the week as a number from 1 through 7.

Syntax

SELECT @@DATEFIRST 

Result

 7  (The U.S. English default is 7, which is Sunday.)

SET DATEFIRST {number | @number_var} provides for setting the first day of the week where {number | @number_var} indicates the day of the week.

The SET DATEFIRST setting is determined at executes or run time and cannot be set at parse time.

@@DBTS

@@DBTS returns the value of the current rowversion (timestamp) data type for the database. The rowversion is guaranteed to be unique in the database. @@DBTS returns a varbinary which is the last-used rowversion value of the current database. A new rowversion value is generated when a row with a rowversion column is inserted or updated.

Syntax

 SELECT @@DBTS

Result

The current timestamp from the current database.

Any INSERT, UPDATE, or CREATE queries will internally increase the rowversion value. @@DBTS value does not roll back when a transaction rolls back or when an INSERT or UPDATE query causes an error.

@@LANGID

@@LANGID helps the user view information about local language settings, including language ID numbers.

Syntax

SELECT @@LANGID  

Result

 0 (The langid for U.S. English is zero.)

To get a list of supported languages along with their language IDs, execute the stored procedure sp_helplanguage with no parameters passed.

Example

Change the settings to use the French language.  If we then query for the language ID, it will return as follows: 

SET LANGUAGE ‘French’  
    SELECT @@LANGID AS 'Language ID' 

Result

Returns Language ID of 2

@@LANGUAGE

@@LANGUAGE helps the user view information about language settings, including valid official language names.

Syntax

 SELECT @@LANGUAGE 

Result

US_English as the language for the current session

The session Language determines the datetime formats and system messages.  The Language can be set using

SET LANGUAGE {[N] 'language' | @language_var}

Where [N]'language' | @language_var is the name of the language as stored in sys.syslanguages table. This argument can be either Unicode or DBCS converted to Unicode. To specify a language in Unicode, use N'language'. If specified as a variable, the variable must be of type sysname.

SET LANGUAGE implicitly uses SET DATEFORMAT to set the date format.

@@LOCK_TIMEOUT

@@LOCK_TIMEOUT helps the user access the current lock timeout setting, the maximum time in milliseconds that a statement will wait for an unavailable resource in the current session.

Syntax    

SELECT @@LOCK_TIMEOUT

Result

-1.  (Default, if SET LOCK_TIMEOUT not yet run in the current session.)

 SET LOCK_TIMEOUT timeout_period

Where timeout_period is the number of milliseconds that pass before Microsoft SQL Server returns a locking error.

Whenever the wait for a lock exceeds the time-out value, an error is returned.  If the timeout_period is set to 0, a message is triggered as soon as a lock is encountered.

@@MAX_CONNECTIONS

@@MAX_CONNECTIONS helps the user access the maximum number of simultaneous user connections allowed in the current session of SQL Server. The maximum number returned may not be the number configured, for example, when the number currently configured is limited due to application or hardware limitations.

Syntax    

SELECT @@MAX_CONNECTIONS   

Result

32767.  (Default, if not yet configured for fewer connections than the maximum.)

The actual number of user connections allowed also depends on the version of SQL Server. sp_configure can be used to reconfigure SQL Server for fewer connections.

@@MAX_PRECISION

@@MAX_PRECISION helps the user view the precision level set in the current session.  This precision setting is used by decimal and numeric data types.

Syntax

SELECT @@MAX_PRECISION

Result

38 (Default set in the server.)

@@NESTLEVEL

@@NESTLEVEL helps the user view the nesting level on the local server.  Initially the value is zero.  As the current stored procedure executes, this value changes.

Syntax   

SELECT @@NESTLEVEL

Whenever a stored procedure makes a call, the nesting level is incremented.  It may call another stored procedure, or it may execute managed code.   When a stored procedure references a common language runtime (CLR) routine, type, or aggregate, it is executing managed code.  In all of these cases, the nesting level is incremented until the maximum of 32 is reached.  When this maximum is exceeded, the transaction terminates.

Result
@@NESTLEVEL can be executed within a Transact-SQL string.  In this case, the value returned by this function will be 1 + the current nesting level. If @@NESTLEVEL is executed dynamically, the value returned will be 2 + the current nesting level.  Dynamic execution is accomplished by using sp_executesql.

@@OPTIONS

@@OPTIONS helps the user access information about the current SET options.

Syntax

SELECT @@OPTIONS 

Result
5496.

In general, SET options are modified as a whole using the configuration option of sp_configureuser options. When a user logs on, the user is assigned a default configuration by the system administrator.  As a result, all users have an @@OPTIONS function representing the configuration. The SET statement can be used to change the language or to change query-processing configuration options for the user.

@@REMSERVER

@@REMSERVER helps the user view the name of the remote SQL Server database server.  The name is formatted in the login record and can be checked by a stored procedure, but only for the database server that initiated the procedure itself.

Syntax

SELECT @@REMSERVER

Result
Displays the name of the remote sever, if connected.

@@SERVERNAME

@@SERVERNAME helps the user view the name of the local SQL server.  During installation, SQL Server Setup sets the server name to the computer name. It can be changed by using sp_addserver and then restarting SQL Server.

Syntax    

SELECT @@SERVERNAME
   

Result
Displays the name of the local sever.

With multiple instances of SQL Server installed, @@SERVERNAME returns the following local server name information, if the local server name has not been changed since setup.

Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer. But @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

@@SERVERNAME uses either the sp_addserver or the sp_dropserver stored procedure.

@@SERVICENAME

@@SERVICENAME helps the user view the name of the registry key under which SQL Server is running. 'MSSQLSERVER' is the default returned.  If the current instance has been named, then @@SERVICENAME returns the instance name.

Syntax  

SELECT @@SERVICENAME

Result
MSSQLSERVER.(Default, if not a named instance.)

@@SPID

@@SPID helps the user view the server process identifier (ID) of the current user process, also known as the session ID. By using the procedure sp_who, the SPID, hostname, and other details can be viewed.

Syntax

SELECT @@SPID

Result
52.

@@TEXTSIZE

@@TEXTSIZE helps the user view the current value of the TEXTSIZE option of the SET statement.  This option specifies the maximum length, in bytes, of text or image data returned by a SELECT statement.  The default size is 4096 bytes.

The TEXTSIZE can be set as follows:

Syntax 

SELECT @@TEXTSIZE  

Result
4096.

Example 

SET TEXTSIZE 0  
    SELECT @@TEXTSIZE AS 'Text Size'  
    4096 bytes (default size)  
    SET TEXTSIZE 2048  
    SELECT @@TEXTSIZE AS 'Text Size'       

Result
2048

@@VERSION

@@VERSION helps the user view the date, version, and processor type for the current installation of Microsoft® SQL Server. The information returned by @@VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, which provides more detailed information.

Syntax   

SELECT @@VERSION

Result
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2).