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 select statement
Declaring a Local Variable:
Use the following syntax to declare a local variable's name and datatype:
declare @variable_name datatype
[, @variable_name datatype]...
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.
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:
1) @@CONNECTIONS : The number of logins or attempted logins since SQL Server was last started.
select getdate() as today , @@connections as login_attempts
2)@@CPU_BUSY : The Number of milliseconds CPU has spent Working since SQL Server was last started
select @@CPU_BUSY as cputime_utilized
3)@@CURSOR_ROWS : Number of rows currently in the last opened cursor (for the current connection)
select @@CURSOR_ROWS AS ROWS_CURSOR
4)@@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).
select @@DATEFIRST AS FIRSTDAY
5)@@DBTS : The current value of TIMESTAMP for the current database.
select @@DBTS AS TIMESTAMP_db
6)@@ERROR : The error number for the last T-SQL statement executed. If this value is zero than there were no error.
select @@ERROR AS ERROR_NO7)@@FETCH_STATUS : Fetch status of the last FETCH statement of the connection, executed against any cursor opened on the current connection.
select @@FETCH_STATUS 8)@@IDENTITY : Returns the last IDENTITY value inserted. If there haven't been any IDENTITY values inserted than this variable is NULL.
select @@IDENTITY AS LAST_IDENTITYVALUE
9)@@IDLE : Number of milliseconds SQL Server has been idle since it was last started
select @@IDLE AS IDLE_MILLISECONDS
10)@@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.
select @@IO_BUSY AS IOBUSY_MILLISECONDS
These are the first 10 global variables in the sql server 2008 , in the next article we will learn the next 10 global variables in the sql server 2008
click below link to go to global variables in Sql Server 2008 Part-2