Monday, December 28, 2009

Built-in Functions - Configuration Functions

Configuration functions return a single value pertinent to the configuration of the current SQL Server instance; these functions do not accept any parameters. The value returned from some of the configuration functions can be changed by altering one of the configuration parameters by using the SET statement, executing sp_configure, or sp_dboption. All configuration functions are nondeterministic.

@@DATEFIRST Function


Returns the value of SET DATEFIRST (with DATEFIRST, 1 stands for Monday, 2 stands for Tuesday, and so on). Example:

SELECT @@DATEFIRST

@@DBTS Function


Current value of TIMESTAMP data type within the current database. Example:

 
SELECT @@DBTS

Results:

------------------  
0x00000000000007D4

@@LANGID Function


Language id of the current language in use. You can modify the language to be used by running SET LANGUAGE statement. Example:

SET LANGUAGE deutsch  GO  SELECT @@LANGID

Results:

Die Spracheneinstellung wurde auf Deutsch geändert.   
------  
1

 

@@LANGUAGE Function

Language name of the current language in use. Example:

SELECT @@LANGUAGE

@@LOCK_TIMEOUT Function

Lock timeout setting for current connection. You can alter the value of this function by executing SET LOCK_TIMEOUT statement by specifying maximum amount of time (in milliseconds) SQL Server should wait before canceling the statement for which locks are not available. Default value is -1 meaning that SQL Server will wait indefinitely. Example:

SELECT @@LOCK_TIMEOUT

@@MAX_CONNECTIONS Function

Maximum connections allowed on the current instance of SQL Server. You can alter the value returned by this function by executing sp_configure. Maximum number of connections allowed on any SQL Server instance is 32767 but this number will be limited by your hardware and / or edition of SQL Server you're using. Example:

SELECT @@MAX_CONNECTIONS

@@MAX_PRECISION Function

Precision level used by default for NUMERIC and DECIMAL data types. Default value is 38. Example:

SELECT @@MAX_PRECISION

@@NESTLEVEL Function

Nesting level of current procedure execution (incremented each time one stored procedure executes another). Example:

SELECT @@NESTLEVEL

@@OPTIONS Function

SET options for the current connection. The value returned by this function can be altered by executing sp_configure and changing user options. This function returns an integer representation of the binary value that stores connection settings. Example:

SELECT @@OPTIONS

@@REMSERVER Function

Returns the name of the remote SQL Server from which a stored procedure is executed. This function is deprecated and will not be supported in future versions of SQL Server. Example:

SELECT @@REMSERVER

@@SERVERNAME Function

Returns the name of the local SQL Server. Example:

SELECT @@SERVERNAME

@@SERVICENAME Function

Name of the registry key under which SQL Server is running. Returns the instance name for non-default instances. For the default instance returns MSSQLSERVER. Example:

SELECT @@SERVICENAME

Results:

-------------------  
SQLSERVER2005

@@SPID Function

Current server process id (spid). Example:

SELECT @@SPID

@@TEXTSIZE Function

Current value of TEXSIZE option. The value returned by this function can be altered by executing SET TEXTSIZE statement to specify the upper limit for the length of columns with VARCHAR(MAX), NVARCHAR(MAX), NTEXT, TEXT, IMAGE and VARBINARY(MAX) data types. Example:

SELECT @@TEXTSIZE

@@VERSION Function

Date, version, and processor type for the current instance of SQL Server. Example:

SELECT @@VERSION

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

No comments:

Post a Comment