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
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
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.
Thanks To
Conclusion
I hope that all of our friends will contribute. Thanks :)
History
