Global Variables in SQL Server 2012

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

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