SQL Server Local and Global Variables

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.

In SQL Server they are two types of variables local variables and global variables .

Local Variables : A local variable is defined with a declare statement and assigned an initial value within the statement batch where it is declared with a set or select statement.

Declaring a Local Variable:

To declare a variable in T-SQL, you use the DECLARE statement:

DECLARE <@var_nam> <data_type>

For example, you would declare the variable @i as an integer by using the statement

DECLARE @i int

T-SQL also supports specifying the AS keyword between the variable's name and its data type, as in the following statement:

DECLARE @i AS int

The variable name must be preceded by the @ sign and conform to the rules for identifiers. The datatype can be any datatype except text, image, or sysname.

Assigning Values to Variables :

SET @i = 12;

Global variables: Global variables are SQL Server-supplied variables that have system-supplied values.The global variables are reserved for system-wide usage. You cannot explicitly create or modify global variables - SQL Server maintains them implicitly.

Predefined global variables are distinguished from local variables by having two @ signs preceding their names, for example, @@error, @@rowcount. Users cannot create global variables, and cannot update the value of global variables directly in a select statement.

List of first 10 Global Variables and their Usage are included below:

@@connections: The number of logins or attempted logins since SQL Server was last started.

 Example:

select getdate() as today , @@connections as login_attempts

Output:

today                   login_attempts

2015-04-03 14:41:15.687 5815906

@@CPU_BUSY     : The Number of milliseconds CPU has spent Working since SQL Server was last started.

Example:

select @@CPU_BUSY as cputime_utilized

@@CURSOR_ROWS: Number of rows currently in the last opened cursor (for the current connection).

Example:

select @@CURSOR_ROWS  AS  ROWS_CURSOR

@@DATEFIRST: First day of the week. NOTE: unlike what you'd expect @@DATEFIRST returns 1 for Monday, 2 for Tuesday, etc. Default is 7(Sunday).

Example:

select  @@DATEFIRST  AS  FIRSTDAY

@@DBTS: The current value of TIMESTAMP for the current database.

Example:

select @@DBTS AS TIMESTAMP_db

@@ERROR: The error number for the last T-SQL statement executed. If this value is zero than there        were no error.

Example:

select @@ERROR AS ERROR_NO

@@FETCH_STATUS: Fetch status of the last FETCH statement of the connection, executed against any cursor opened on the current connection.

Example:

select @@FETCH_STATUS

@@IDENTITY: Returns the last IDENTITY value inserted. If there haven't been any IDENTITY values inserted than this variable is NULL.

Example:

select @@IDENTITY AS LAST_IDENTITYVALUE

@@IDLE: Number of milliseconds SQL Server has been idle since it was last started.

Example:

select @@IDLE AS IDLE_MILLISECONDS

@@IO_BUSY: The amount of time, in ticks, that SQL Server has spent doing input and output operations since it was last started. i.e Number of milliseconds SQL Server has spent performing Input and Output (IO) operations since it was last started.

Example:

select @@IO_BUSY AS IOBUSY_MILLISECONDS