Global variables in SQL Server

Global variables are pre-defined system functions. Their names begin with an @@ prefix. SQL Server provides a massive number of global variables, which are very effective to use in our regular Transact-SQL. Global variables represent a special type of variable. The server always maintains the values of these variables. All the global variables represent information specific to the server or a current user session. Global variables return various pieces of information about the current user environment for SQL Server.

Below is the list of Global variables

1.       @@CONNECTIONS

2.       @@MAX_CONNECTIONS

3.       @@CPU_BUSY

4.       @@ERROR  

5.       @@IDENTITY

6.       @@IDLE

7.       @@IO_BUSY

8.       @@LANGID  

9.       @@LANGUAGE

10.   @@MAXCHARLEN

11.   @@PACK_RECEIVED  

12.   @@PACK_SENT

13.   @@PACKET_ERRORS

14.   @@ROWCOUNT  

15.   @@SERVERNAME 

16.   @@SPID

17.   @@TEXTSIZE 

18.   @@TIMETICKS

19.   @@TOTAL_ERRORS

20.   @@TOTAL_READ / @@TOTAL_WRITE

21.   @@TRANCOUNT

22.   @@VERSION  

@@CONNECTIONS

It returns the number of logins or attempted logins since SQL Server was last started.

select @@CONNECTIONS AS 'Login Attempts'

@@MAX_CONNECTIONS

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

SELECT @@MAX_CONNECTIONS AS 'Max Connections'

@@CPU_BUSY

The amount of time, in ticks, that the CPU has spent doing SQL Server work since the last time SQL Server was started.

SELECT @@CPU_BUSY

@@ERROR  

Commonly used to check the error status (succeeded or failed) of the most recently executed statement. It contains 0 if the previous transaction succeeded; otherwise, it contains the last error number generated by the system.

In the following example, let's assume that an error will occur during the Update statement. @@error will contain the error code only until the next statement is executed; even the command for reading the @@error value will reset it. If it was completed successfully, SQL Server will set @@error to 0. The only way to preserve the @@error value is to immediately read it and store it in a local variable; then it can be used for error handling.

I want to update the sal value on the EMP table using below sql script

update Emp set sal='Bagesh' where EmpId=1

Sal type is int. defiantly it will be throw error.

See below

Here the value will change of @@ERROR variable. Now we check the value of @@ERROR

@@IDENTITY

It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in our current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If we have a trigger on a table that causes an identity to be created in another table, we will get the identity that was created last, even if it was the trigger that created it.

Let’s see how to create identity

create table product (  productID int identity(1,1),  productName varchar(50),  cost int  )

Now I am inserting some records

insert into product values ('Pen',50)  insert into product values ('Book',50)  insert into product values ('Note Book',50)  insert into product values ('Copy',50)

Product ID is auto incremented

select * from product select @@IDENTITY As 'Current ProductID'

If I insert a record the value of @@IDENTITY will be change. I have inserted 2 more records see the result.

@@IDLE

The amount of time, in ticks, that SQL Server has been idle since it was last started.

select @@IDLE

@@IO_BUSY

The amount of time, in ticks, that SQL Server has spent doing input and output operations since it was last started.

select @@IO_BUSY

@@LANGID  

The local language id of the language currently in use (specified in syslanguages.langid).

select @@LANGID

@@LANGUAGE

Return the current language

SELECT @@LANGUAGE AS 'Language Name'

@@MAXCHARLEN

The maximum length, in bytes, of a character in SQL Server's default character set.

SELECT @@MAX_PRECISION AS 'Max Precision'

@@PACK_RECEIVED

The number of input packets read by SQL Server since it was last started.

SELECT @@PACK_RECEIVED AS 'Packets Received'

@@PACK_SENT

The number of output packets written by SQL Server since it was last started.

SELECT @@PACK_SENT AS 'Pack Sent'

@@PACKET_ERRORS 

The number of errors that have occurred while SQL Server was sending and receiving packets.

SELECT @@PACKET_ERRORS AS 'Packet Errors'

@@ROW COUNT 

The number of rows affected by the last command. @@rowcount is set to 0 by any command which does not return rows, such as an if statement. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request.

@@SERVERNAME 

Return the server name.

SELECT @@SERVERNAME as 'Sarver Name'

@@SPID 

Return the server process ID number of the current process.

SELECT @@SPID AS 'ID'

@@TEXTSIZE 

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.

SELECT @@TEXTSIZE AS 'Text Size'

@@TIMETICKS

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

SELECT @@TIMETICKS AS 'Time Ticks'

@@TOTAL_ERRORS 

The number of errors that have occurred while SQL Server was reading or writing.

SELECT @@TOTAL_ERRORS AS 'Errors'

  

@@TOTAL_READ / @@TOTAL_WRITE

The number of disk reads by SQL Server since it was last started.

SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes'

  

@@TRANCOUNT

The nesting level of transactions. Each begin transaction in a batch increments the transaction count. When we query @@trancount in chained transaction mode, its value is never zero since the query automatically initiates a transaction.

PRINT @@TRANCOUNT --  The BEGIN TRAN statement will increment the --  transaction count by 1. BEGIN TRAN     PRINT @@TRANCOUNT     BEGIN TRAN         PRINT @@TRANCOUNT --  The COMMIT statement will decrement the transaction count by 1.     COMMIT     PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT

@@VERSION 

The date of the current version of SQL Server.

SELECT @@VERSION AS 'SQL Server Version'