Scalar Configuration Functions

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

The following scalar functions return information about current configuration option settings,

@@DATEFIRST
Returns the current value, for the session, of SET DATEFIRST.
e.g. SELECT @@DATEFIRST
you can change first day of week setting by using the following command.
SET DATEFIRST [integer]
e.g Set DATEFIRST 5
It will set Firday as first day of week.

@@DBTS
It returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.
e.g. SELECT @@DBTS

@@LANGID
Returns the local language identifier (ID) of the language that is currently being used.
e.g. SELECT @@LANGID

To view information about language settings, including language ID numbers, you can use the following system procedure.
EXEC SP_HELPLANGUAGE

You can change language settings as
e.g. SET LANGUAGE 'Italian'

@@SERVERNAME
Returns the name of the local server running SQL Server.
e.g. SELECT @@SERVERNAME

@@LANGUAGE
Returns the name of the language currently being used.
e.g. SELECT @@LANGUAGE
Remaining details will be the same as defined under @@LANGID

@@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.
e.g. SELECT @@SERVICENAME

@@LOCK_TIMEOUT
Returns the current lock time-out setting in milliseconds for the current session.
It 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.

It returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the current session.
e.g. SELECT @@LOCK_TIMEOUT

@@SPID
Returns the session ID of the current user process.
It can be used to identify the current user process in the output of sp_who.
e.g. SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

@@TEXTSIZE
Returns the current value of the TEXTSIZE option of the SET statement. This specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, or image data that a SELECT statement returns.
The default size is 4096 bytes. The maximum size that @@TEXTSIZE returns is 2^31-1 bytes.

You can change its setting as:

SET TEXTSIZE [integer]

e.g.
Displays current value
SELECT @@TEXTSIZE AS 'Text Size'
Setting new value
SET TEXTSIZE 2048
Displays new value
SELECT @@TEXTSIZE AS 'Text Size'

@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of 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.
e.g. SELECT @@VERSION