Global Variables in SQL Server 2012
This article contains the most commonly used global variables in SQL Server under a single article with examples.
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:
This variable returns the number of login attempts since SQL Server was last started. It returns an integer value.
select @@CONNECTIONS as 'Number of Login Attempts'
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.
SELECT * From UserDetail
if(@@ERROR <> 0)
print 'Error Found'
print 'Error not Found'
@@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:
Now for using the @@IDENTITY variable
INSERT INTO [dbo].[UserDetail]
Select @@IDENTITY as identityvalue
This variable returns the number of milliseconds SQL Server has been idle since it was last started.
select @@IDLE as 'idle milliseconds Time'
This system function returns the number of milliseconds the CPU has spent working since SQL Server was last started. It returns an integer value.
select @@CPU_BUSY as 'Busy milliseconds Time'
This system function returns the name of the language that is currently used by the SQL Server.
SELECT @@LANGUAGE as 'Language'
This variable returns the number of rows affected by the last Transact-SQL statement.
SELECT * FROM UserDetail
SELECT @@rowcount as 'Count Number of Rows affected'
This variable returns the name of the service under which SQL Server is running.
Select @@SERVICENAME as 'ServiceName'
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.
SELECT @@Total_ERRORS as 'number of disk read-write errors'
This variable returns the current version of the SQL Server Software.
SELECT @@VERSION as 'SQL Server Version'
This function retrieves the name of the database server the application is linked to.
SELECT @@SERVERNAME as 'Server Name'