Different types of variables in SQL Server

Introduction:

In this article i will explain Different types of Variables in SQL Server.

Description:

In previous articles i explained about joins, Batches, Stored procedure, Functions, Difference between Stored procedure and Function, and Triggers in SQL Server. Now i will explain Different types of Variables in SQL Server.

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 valu is provided as part of the declaration.

SQL Server has two types of variables.

Local variables - Local Variable names prefixed with '@' sumbol.
Global Variables - Global Variable names prefixed with '@@' symbols.

Local Variables:

Declare and Assign values:

Syntax:

Declare <@local_variable_name> [AS] data_type,...

Example:

Declare @a int

Set @a = 10;

--(or)

Select @a = 10;

Select @a;

SQL SERVER 2008 – 2012 – Declare and Assign Variable in Single Statement:

Declare @a int = 10, @b int = 10, @Date datetime = GetDate()

Select @a as a, @b as b, @Date as [Date];

Output:

Global Variables:

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.

Note:

They are system-defined functions and you cannot declare them.

Global variables available in SQL Server 2008 are as follows:

1. @@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:
2. @@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:
3. @@CURSOR_ROWS
4. @@DATEFIRST
5. @@DBTS
6. @@DEF_SORTORDER_ID
7. @@DEFAULT_LANGID
8. @@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. Return type: int Example(1): `Declare @i int; Set @i = 10/0; print @@Error IF @@ERROR <> 0 PRINT 'Your error message'; Else PRINT 'Your success message'` Output: Note: 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. When ever error occured then the @@error value updated with error_number. In the above example, it prints @@error value and then it printed else part message only. Why because, print is also a statement, it is succeeded. So, system resets @@error value to zero. Get Error Details: To get details of a particular error see the following example. Example(2): `Declare @a int, @Error_Number int; Begin try Set @a = 10/0; Set @Error_Number = @@ERROR End try Begin Catch IF @@ERROR != 0 Select Error_Number() as [Error Number], Error_Message() as [Error Description] End catch` Output: Note: Here Error_Number() and Error_Message() are predefined system functions.
9. @@FETCH_STATUS
10. @@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)
11. @@IDLE
The amount of time, in ticks, that SQL Server has been idle since it was last started. Return type: int
12. @@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
13. @@LANGID
The local language id of the language currently in use (specified in syslanguages.langid). Return type: smallint
14. @@LANGUAGE
The name of the language currently in use (specified in syslanguages.name). Return type: nvarchar
15. @@LOCK_TIMEOUT
16. @@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:
17. @@MAX_PRECISION
18. @@MICROSOFTVERSION
19. @@NESTLEVEL
20. @@OPTIONS
21. @@PACK_RECEIVED
The number of input packets read by SQL Server since it was last started. Return type: int
22. @@PACK_SENT
The number of output packets written by SQL Server since it was last started. Return type: int
23. @@PACKET_ERRORS
The number of errors that have occurred while SQL Server was sending and receiving packets. Return type: int
24. @@PROCID
25. @@REMSERVER
26. @@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
27. @@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
28. @@SERVICENAME
29. @@SPID
The server process ID number of the current process. Return type: smallint
30. @@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
31. @@TIMETICKS
The number of microseconds per tick. The amount of time per tick is machine dependent. Return type: int
32. @@TOTAL_ERRORS
The number of errors that have occurred while SQL Server was reading or writing. Return type: int
33. @@TOTAL_READ
34. @@TOTAL_WRITE
35. @@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
36. @@VERSION
The date of the current version of SQL Server. Return type: nvarchar