Global variables are pre-defined system functions. Their names begin with an @@ prefix. The server maintains the values in these variables. Global variables return various pieces of information about the current user environment for SQL Server. Global Variable are automatically updated and interact with the system. So let's have a look at a practical example of how to use @@functions in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
SQL Server provides a massive number of global variables. The following lists some important global variables:
- @@CONNECTIONS
- @@ERROR
- @@IDENTITY
- @@IDLE
- @@CPU_BUSY
- @@LANGUAGE
- @@ROWCOUNT
- @@SERVERNAME
- @@TOTAL_ERRORS
- @@VERSION
- @@SERVERNAME
1. @@CONNECTIONS
This variable returns the number of login attempts since SQL Server was last started. It returns an integer value.
Example
select @@CONNECTIONS as 'Number of Login Attempts'
Output
2. @@ERROR
The error number for the last T-SQL statement executed. If this value is zero than there were no errors otherwise it returns the error.
Example
SELECT * From UserDetail
if(@@ERROR <> 0)
print 'Error Found'
else
print 'Error not Found'
Output
3. @@IDENTITY
@@IDENTITY will return the last identity value entered into a table. @@IDENTITY is not limited to a specific scope. Suppose we create a table and set the identity value to true for a column in the table. After that when we insert data into the table we get the last identity value using @@IDENTITY. If the statement did not affect any tables with identity columns, then @@IDENTITY returns NULL. You can also access the @@IDENTITY value in your application.
Creating Table in SQL Server Database
Now create a table named UserDetail with the columns ID, UserName, CompanyName and Salary. Set the identity property=true for ID. The table looks as in the following:
Now insert some values into this table. The table looks like this:
Example
Now for using the @@IDENTITY variable
INSERT INTO [dbo].[UserDetail]
([UserName]
,[CompanyName]
,[Salary])
VALUES('Ashish','NTPC','15000')
Select @@IDENTITY as identityvalue
Output
4. @@IDLE
This variable returns the number of milliseconds SQL Server has been idle since it was last started.
Example
select @@IDLE as 'idle milliseconds Time'
Output
5. @@CPU_BUSY
This system function returns the number of milliseconds the CPU has spent working since SQL Server was last started. It returns an integer value.
Example
select @@CPU_BUSY as 'Busy milliseconds Time'
Output
6. @@LANGUAGE
This system function returns the name of the language that is currently used by the SQL Server.
Example
SELECT @@LANGUAGE as 'Language'
Output
7. @@ROWCOUNT
This variable returns the number of rows affected by the last Transact-SQL statement.
Example
SELECT * FROM UserDetail
SELECT @@rowcount as 'Count Number of Rows affected'
Output
8. @@SERVERNAME
This variable returns the name of the service under which SQL Server is running.
Example
Select @@SERVICENAME as 'ServiceName'
Output
9. @@ Total_ERRORS
The @@TOTAL_ERRORS function returns the number of disk read/write errors encountered by SQL Server since it was last started. It returns an integer value.
Example
SELECT @@Total_ERRORS as 'number of disk read-write errors'
Output
10. @@VERSION
This variable returns the current version of the SQL Server Software.
Example
SELECT @@VERSION as 'SQL Server Version'
Output
11. @@Servername
This function retrieves the name of the database server the application is linked to.
Example
SELECT @@SERVERNAME as 'Server Name'
Output