SQL Configuration Functions

Next Post
By Admin at 3 Jun 2010, 15:57 PM
  • 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

    1. 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.

    ValueFirst day of the week
    1Monday
    2Tuesday
    3Wednesday
    4Thursday
    5Friday
    6Saturday
    7Sunday(default,U.S.English)


    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

    1. 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

    1. 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:

    1. SET LANGUAGE ‘French’
    2. 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

    1. 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

    1. 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

    1. SELECT @@LOCK_TIMEOUT

    Result

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

    1. 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

    1. 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

    1. 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

    1. 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

    1. 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

    1. 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

    1. 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.

    InstanceServer information
    Default instance'servername'
    Named instance'servername\instancename'
    Virtual server - default instance'virtualservername'
    Virtual server - named instance'virtualservername\instancename'

    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

    1. 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

    1. 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

    1. SELECT @@TEXTSIZE

    Result
    4096.

    Example

    1. SET TEXTSIZE 0
    2. SELECT @@TEXTSIZE AS'Text Size'
    3. 4096 bytes (defaultsize)
    4. SET TEXTSIZE 2048
    5. 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

    1. 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).


    Comments

     

    Post a comment

    Please correct the following: