Global Variables in SQL Server

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 maintain the values of these variables. All the global variables represent information specific to the server or a current user session. 

Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them.

@@CONNECTIONS

@@MAX_CONNECTIONS

@@CPU_BUSY

@@ERROR 

@@IDENTITY

@@IDLE

@@IO_BUSY

@@LANGID 

@@LANGUAGE

@@MAXCHARLEN

@@PACK_RECEIVED 

@@PACK_SENT

@@PACKET_ERRORS

@@ROWCOUNT 

@@SERVERNAME

@@SPID

@@TEXTSIZE

@@TIMETICKS

@@TOTAL_ERRORS

@@TOTAL_READ / @@TOTAL_WRITE

@@TRANCOUNT

@@CONNECTIONS :
The number of logins or attempted logins since SQL Server was last started.
Return type: int

 
Example
SELECT GETDATE() AS 'Today''s Date and Time',
@@CONNECTIONS AS 'Login Attempts'

Output
Today's Date and Time   Login Attempts
----------------------- --------------
2009-08-19 21:44:32.140 1430


@@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''. 
 

Return type: int
 

Example
SELECT @@MAX_CONNECTIONS AS 'Max Connections'
 

Output
Max Connections
---------------
32767


@@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.
Return type: int
 

Example
SELECT @@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) AS 'CPU microseconds',
   GETDATE() AS 'As of' ;

Output
CPU microseconds       As of
---------------------- -----------------------
2812500                2009-08-19 21:47:27.187


@@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. A statement such as: 
Return type: int
 

Example
IF @@ERROR <> 0
    PRINT  'Your error message';
 

Output
Your error message
IF @@ERROR != 0 return causes an exit if an error occurs.Every Transact-SQL statement resets @@error, including print statements or if tests, so the status check must immediately follow the statement whose success is in question.


@@IDENTITY : 
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 IDENTITYvalue for the last row inserted. If the affected table does not contain an IDENTITY column, @@identity is set to 0.

Return type: numeric(38,0)

Example
INSERT INTO [TempE].[dbo].[CaseExpression]
           ([Code])   VALUES (5) 
GO
SELECT @@IDENTITY AS 'Identity';
Output
Identity
---------------------------------------
5 


@@IDLE
The amount of time, in ticks, that SQL Server has been idle since it was last started.
Return type: int

Example
SELECT @@IDLE * CAST(@@TIMETICKS AS float) AS 'Idle microseconds',
   GETDATE() AS 'as of'
Output
Idle microseconds      as of
---------------------- -----------------------
11340000000            2009-08-19 22:07:19.903



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

Return type: int 

Example
SELECT @@IO_BUSY*@@TIMETICKS AS 'IO microseconds',
   GETDATE() AS 'as of'
Output
IO microseconds as of
--------------- -----------------------
5906250         2009-08-19 22:09:44.013



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

Return type: smallint

 
Example

 
SET LANGUAGE 'Italian'
SELECT @@LANGID AS 'Language ID'
SET LANGUAGE 'us_english'
SELECT @@LANGID AS 'Language ID'

Output
L'impostazione della lingua è stata sostituita con Italiano.
Language ID
-----------
6
Changed language setting to us_english.
Language ID
-----------
0



@@LANGUAGE :
The name of the language currently in use (specified in syslanguages.name).
 

Return type: nvarchar
 

Example
SELECT @@LANGUAGE AS 'Language Name';
 

Output
Language Name
-------------
us_english 


@@MAXCHARLEN :
The maximum length, in bytes, of a character in SQL Server's default character set.
Return type: tinyint
Example
SELECT @@MAX_PRECISION AS 'Max Precision'
Output
Max Precision
-------------
38



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

Return type: int

 
Example
SELECT @@PACK_RECEIVED AS 'Packets Received'

Output
Packets Received
----------------
8998



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

Return type: int

 
Example
SELECT @@PACK_SENT AS 'Pack Sent'

Output
Pack Sent
-----------
9413



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

Return type: int
 

Example
SELECT @@PACKET_ERRORS AS 'Packet Errors'
 

Output
Packet Errors
-------------
0



@@ROWCOUNT :
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.
Return type: int
Example
IF @@ROWCOUNT = 0
 PRINT 'Warning: No rows were updated';
Output
'Warning: No rows were updated'



@@SERVERNAME :
The name of the local SQL Server. You must define a server name with sp_addserver, and then restart SQL Server.
 

Return type: varchar
 

ExampleSELECT @@SERVERNAME AS 'Server Name'
 

Output
MY_SERVER_WINDOWS_2003



@@SPID :
The server process ID number of the current process.
 

Return type: smallint
 

Example
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
 

Output
ID     Login Name                                User Name
------ ----------------------------------------------------
55     MY_SERVER_WINDOWS_2003\Administrator          dbo



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

Return type: smallint
 

Example
SET TEXTSIZE 2048

SELECT @@TEXTSIZE AS 'Text Size'
Output
Text Size
-----------
2048



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

Return type: int
 

Example
SELECT @@TIMETICKS AS 'Time Ticks';
 

Output
Time Ticks
-----------
31250



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

Return type: int
 

Example
SELECT @@TOTAL_ERRORS AS 'Errors', GETDATE() AS 'As of'

Output
Errors      As of
----------- -----------------------
0           2009-08-19 22:47:51.937




@@TOTAL_READ / @@TOTAL_WRITE:
The number of disk reads by SQL Server since it was last started.
 

Return type: int
 

Example
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
 

Output
Reads       Writes      As of
----------- ----------- -----------------------
861         91          2009-08-19 23:36:26.763



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

Example
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
 

Output
0
1
2
1
0

@@VERSION :
The date of the current version of SQL Server.
 

Return type: nvarchar

Example
SELECT @@VERSION AS 'SQL Server Version'

Output
Jul  9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)