SQL Server Common configuration functions and their functions

Configuration functions are used to return settings information for configuration options, which are common:

[Email protected] @DATEFIRST

Returns the current value of set Datefirst for the session,set Datefirst represents the first day of the specified week,

U.S. English default 1 corresponds to Monday, 2 corresponds to Tuesday, and so on, 7 corresponds to Sunday , Sunday in the West represents the first day of the week

Example: The following settings are 5, Friday as the first day of the week, and assume today is Friday, that is, today is the first day of Friday

Set 5 Go Select @ @DATEFIRST first day,datepart(DW,getdate as Today

The first day today

------------       ----------
5 1

[Email protected] @LANGID

Returns the local language identifier (ID) of the language currently in use, and if you want to see all the information about the language setting, you can execute the sp_helplanguage without parameters, which is exec sp_helplanguage

[Email protected] @LANGUAGE

Returns the name of the currently used language

[Email protected] @LOCK_TIMEOUT

Returns the lock time-out setting (in milliseconds) for the current session, which can be used to set the maximum time that a statement waits to block a resource, and when a statement waits longer than the length of time set by lock_timeout , lock_timeout The locked statement is automatically canceled and an error message is returned to the application

[Email protected] @MAX_CONNECTIONS

Returns the maximum number of simultaneous user connections that the server instance allows, and the number of user connections that are actually allowed depends on the version of SQL Server that is installed and the limitations of the application and hardware

[Email protected] @OPTIONS

returns information about the current set options, which can be uniformly modified with thesp_configure user options configuration option. Each user has an @ @OPTIONS function that represents its configuration environment. Starting with the first logon, the system administrator assigns a default configuration setting to all users. You can change the language and query processing options with the SET statement.

[Email protected] @REMSERVER

When the remote Microsoft®sql Server™ database server appears in the logon record, its name is returned.

return type

nvarchar (256)

Comments

@ @REMSERVER enables a stored procedure to view the name of the database server on which it runs.

Example

The following example creates a procedure named Check_server, which returns the remote server name.

CREATE PROCEDURE Check_server  as SELECT @ @REMSERVER

The stored procedure is created on the local server SEATTLE1 . The user logs on to the remote server LONDON2 , and then runs check_server.

exec Seattle1...check_server

Here is the result set:

---------------LONDON2

[Email protected] @SERVERNAME

Returns the name of the local server running Microsoft®sql Server™.

The SQL Server Setup program sets the server name to the computer name at installation time. You can change the @ @SERVERNAMEby using sp_addserver and then restarting SQL Server. However, this approach is not usually required.

When multiple instances of SQL Server are installed, if the local server name has not changed since it was installed, the @ @SERVERNAME returns the following local server name information.

Example	Server Information
Default instance	'servername'
Named instances	'servername\InstanceName'
Virtual Server-default instance	'virtualservername'
Virtual server-named instance	'virtualservername\instanceservername'

Although the @ @SERVERNAME function and the SERVERNAME property of the serverproperty function may return strings of similar format, the information will vary. The SERVERNAME property automatically reports changes to the computer network name.

By contrast,@ @SERVERNAME not report this change . @ @SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

[email protected]@SERVICENAME

Returns the registry key name under which the server is running. If the current instance is the default instance, the @ @SERVICENAME returns MSSQLSERVER, or the function returns the instance name if the current instance is a named instance.

[email protected] @SPID

Returns the server process session ID of the current user process.

@ @SPID can identify the current user process in sp_who output results.

Example

The following example returns the process ID, login name, and user name of the current user process.

SELECT @ @SPID  as ' ID ' System_user  as ' Login Name ' USER  as ' User Name '

Here is the result set:

ID     Login Name       User Name  -----  -------------    -----------11     sa               dbo     

[Email protected] @VERSION

<img src= "http://counter.yesky.com/counter.shtml? cid=54197& Aid=-1&refer=noscriptcounter&cur= Noscriptcounter "border= ' 0 ' width= ' 0 ' height= ' 0 '/>

Returns the date, version, and processor type of the current installation of Microsoft®sql Server™.

The information returned by the @ @VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, but the xp_msver stored procedure provides more detailed information and more readable results.

Example:

SELECT @ @VERSION

return Result:

Microsoft SQL Server (SP1)-11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise edition:core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200:) (Hypervisor)

SQL Server Common configuration functions and their functions