Reader Level:
ARTICLE

Global Variables in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server November 02, 2012
This article contains the most commonly used global variables in SQL Server under a single article with examples.
  • 1
  • 0
  • 7092

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:

  1. @@CONNECTIONS
  2. @@ERROR  
  3. @@IDENTITY
  4. @@IDLE
  5. @@CPU_BUSY
  6. @@LANGUAGE
  7. @@ROWCOUNT  
  8. @@SERVERNAME 
  9. @@TOTAL_ERRORS
  10. @@VERSION  
  11. @@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

Connection-Variable-in-SQL-Server.jpg

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

Error-Variable-in-SQL-Server.jpg

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:

identity-property-in-SQL-Server.jpg

Now insert some values into this table. The table looks like this:

Table-in-SQL-Server.jpg

Example

Now for using the @@IDENTITY variable

INSERT INTO [dbo].[UserDetail]

           ([UserName]

           ,[CompanyName]

           ,[Salary])

     VALUES('Ashish','NTPC','15000')

     Select @@IDENTITY as identityvalue

 

Output

Identity-Variable-in-SQL-Server.jpg

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

Idle-Variable-in-SQL-Server.jpg

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

Busy-Variable-in-SQL-Server.jpg

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

Language-Variable-in-SQL-Server.jpg

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

RowCount-Variable-in-SQL-Server.jpg

8. @@SERVERNAME 

This variable returns the name of the service under which SQL Server is running.

Example

Select @@SERVICENAME as 'ServiceName'

Output

ServerName-Variable-in-SQL-Server.jpg

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

Total_Error-Variable-in-SQL-Server.jpg

10. @@VERSION  

This variable returns the current version of the SQL Server Software.

Example

SELECT  @@VERSION  as 'SQL Server Version'

Output

Version-Variable-in-SQL-Server.jpg

11. @@Servername

This function retrieves the name of the database server the application is linked to.

Example

SELECT  @@SERVERNAME  as 'Server Name'

Output

ServerName-Variable-in-SQL-Server.jpg

COMMENT USING

Trending up