Introduction

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.

Objective

The main objective of this article is to put all mostly used global variables in SQL Server 2005/2008 under a single article. This article is a common place for all those Global variables with proper examples.

Table of Contents

@@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 IDENTITY value for the last row inserted. If 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)

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

Example

SELECT @@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)

Message to All Silver Members and Above

This Table of Contents and article are editable by all Silver members and above. What I want you to do is replace the entries in the Table of Contents, add as many as you are aware of on SQL Server 2005 or above. This will really help beginners to find all of them under a single article.