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:
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:
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):
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):
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:
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 |
-
CreatedDec 22, 2013
-
UpdatedOct 03, 2020
-
Views2,997