Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : Global Variables

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 447
    Comment on it

    SQL Server provides a number of global variables, which are very useful.The values of these variables is maintained by the server. All the global variables represent information specific to the server or a current user session.The names of global variables begin with @@ prefix.Following are the major global variables :


    1) @@CONNECTIONS

    It returns the number of logins/(attempted logins) since SQL Server was last started.

    Return type: int



    2) @@MAX_CONNECTIONS

    It returns the the maximum number of simultaneous connections that can be made with SQL Server in this computer environment. The user can use (sp_configure ''number of user connections'') to configure SQL Server for any number of connections less than or equal to the value of @@max_connections.

    Return type: int



    3) @@CPU_BUSY

    It returns the amount of time(in ticks) that the CPU has spent doing work since the last time SQL Server was started.

    Return type: int



    4) @@ERROR

    It is commonly used to check the error status of the most recently executed statement. It contains 0 if the previous transaction succeeded, else it contains the last error number generated by the system.

    Return type: int



    5) @@IDENTITY

    It returns the last value inserted into an IDENTITY column by an INSERT or SELECT INTO statement. @@identity is reset each time a row is inserted into a table. If a statement inserts multiple rows, @@identity reflects the IDENTITY value for the last row inserted. In case the affected table does not contain an IDENTITY column, @@identity is set to 0.

    The value of @@identity is not affected by the failure of an insert or select into statement, or the rollback of the transaction that contained it. @@identity retains the last value inserted into an IDENTITY column, even if the statement that inserted it fails to commit.

    Return type: numeric(38,0)



    6) @@IDLE

    It returns the amount of time(in ticks) that SQL Server has been idle since it was last started.

    Return type: int



    7) @@IO_BUSY

    It returns the amount of time(in ticks) that SQL Server has spent doing input/output operations after it was last started.

    Return type: int



    8) @@LANGID

    It returns the local language id of the language currently in use , specified in syslanguages.langid.

    Return type: smallint



    9) @@LANGUAGE

    It returns the name of the language currently in use ,specified in syslanguages.name.

    Return type: nvarchar



    10) @@MAXCHARLEN

    It returns the maximum length, in bytes, of a character in SQL Server's default character set.

    Return type: tinyint



    11) @@PACK_RECEIVED

    It returns the number of input packets read by SQL Server from the time it was last started.

    Return type: int



    12) @@PACK_SENT

    It returns the number of output packets written by SQL Server from the time it was last started.

    Return type: int



    13) @@PACKET_ERRORS

    It returns the number of errors that have occurred during sending/receiving of packets by SQL Server.

    Return type: int



    14) @@ROWCOUNT

    It returns the number of rows affected by the last command.In case the command(e.g IF) does not return any rows, @@rowcount is set to 0. For cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request.

    Return type: int



    15) @@SERVERNAME

    It returns the name of the local SQL Server.The server name must be defined with sp_addserver following which SQL Server must be started.

    Return type: varchar



    16) @@SPID

    It returns the server process ID of the current process.

    Return type: smallint



    17) @@TEXTSIZE

    It returns the current value of the set textsize option, which specifies the maximum length, in bytes, of text or image data to be returned with a select statement. Defaults to 32K.

    Return type: smallint



    18) @@TIMETICKS

    It returns the number of microseconds per tick. The amount of time per tick is machine dependent.

    Return type: int



    19) @@TOTAL_ERRORS

    It returns the number of errors that have occurred during read/write by SQL Server.

    Return type: int



    20) @@TOTAL_READ / @@TOTAL_WRITE

    It returns the number of disk reads by SQL Server from the time it was last started.

    Return type: int



    21) @@TRANCOUNT

    It returns the nesting level of transactions. Inside a batch each begin transaction increments the transaction count. In case you query @@trancount in a chained transaction mode, its value will never be zero becaue the query automatically initiates a transaction.

    Return type: int



    22) @@VERSION

    It returns the current version of SQL Server.

    Return type: nvarchar

    Ref : https://code.msdn.microsoft.com/Global-Variables-in-SQL-749688ef

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: