SQL Server Helper - Configuration Functions

Tip of the Day : Example Uses of the CHOOSE Logical Function

Built-in Functions

Home > Functions > SQL Server Configuration Functions

SQL Server Configuration Functions

SQL Server configuration functions return information about current configuration option settings.

All configuration functions are non-deterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.

Function	Description
@@DATEFIRST	Returns the current value, for a session, of SET DATEFIRST, which specifies the first day of the week. Syntax: @@DATEFIRST
@@DBTS	Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database. A new timestamp value is generated when a row with a timestamp column is inserted or updated. Syntax: @@DBTS
@@LANGID	Returns the local language identifier (ID) of the language that is currently being used. Syntax: @@LANGID
@@LANGUAGE	Returns the name of the language currently being used. Syntax: @@LANGUAGE
@@LOCK_TIMEOUT	Returns the current lock time-out setting in milliseconds for the current session. SET LOCK_TIMEOUT allows an application to set the maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled, and an error message is returned to the application. Syntax: @@LOCK_TIMEOUT
@@MAX_CONNECTIONS	Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured. The actual number of user connections allowed also depends on the version of SQL Server that is installed and the limitations of the applications and hardware. To reconfigure SQL Server for fewer connections, use sp_configure. Syntax: @@MAX_CONNECTIONS
@@MAX_PRECISION	Returns the precision level used by decimal and numeric data types as currently set in the server. By default, the maximum precision returns 38. Syntax: @@MAX_PRECISION
@@NESTLEVEL	Returns the nesting level of the current stored procedure execution (initially 0) on the local server. Each time a stored procedure calls another stored procedure or executes managed code by referencing a common language runtime (CLR) routine, type or aggregate, the nesting level is incremented. When the maximum of 32 is executed, the transaction is terminated. Syntax: @@NESTLEVEL

Function	Description
@@OPTIONS	Returns information about the current SET options. SET options can be modified as a whole by using the sp_configure user options configuration option. Each user has an @@OPTIONS function that represents the configuration. When first logging on, all users are assigned a default configuration set by the system administrator. Syntax: @@OPTIONS
@@REMSERVER	Returns the name of the remote SQL Server database server as it appears in the login record. @@REMSERVER enables a stored procedure to check the name of the database server from which the procedure is run. Syntax: @@REMSERVER
@@SERVERNAME	Returns the name of the local server that is running SQL Server. SQL Server Setup sets the server name to the computer name during installation. To change the name of the server, use sp_addserver, and then restart SQL Server. Syntax: @@SERVERNAME
@@SERVICENAME	Returns the name of the registry key under which SQL Server is running. @@SERVICENAME returns 'MSSQLSERVER' if the current instance is the default instance; this function returns the instance name if the current instance is a named instance. Syntax: @@SERVICENAME
@@SPID	Returns the session ID of the current user process. This is called the server process ID in earlier versions of SQL Server. @@SPID can be used to identify the current user process in the output of sp_who. Syntax: @@SPID
@@TEXTSIZE	Returns the current value of TEXTSIZE option. SET TEXTSIZE specifies the size of VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, and IMAGE data returned by a SELECT statement. Syntax: @@TEXTSIZE
@@VERSION	Returns the version, processor architecture, build date, and operating system for the current installation of SQL Server. Syntax: @@VERSION