Know Popular Global Variables in SQL Server

Introduction

Dear reader, in my past few articles I have explained C# and .NET concepts, basically a few best practices of C#.NET. You can read those in the following links.

• 5 best practices of C#.NET
• 5 tips to improve performance of C# code

And really I also felt bored with the same topics and have decided to choose a different one. And I was thinking about what to write and finally decided to write about the Global Variables of SQL Server Databases.

It does not matter whether you are a C# .NET developer or enjoy another language, databases are common to all. And being a developer sometimes your responsibility is to write a query or database component. (Yes! I am talking about Stored Procedures, functions, cursors, and so on.)

And I hope most of you are familiar with Global Variables of SQL Server Database but may not be familiar with all variables. And if you are pretty new to Global Variables then the following few lines are only for you.

Global Variable in SQL Server Database

Hmm... You are reading, it means new in Global Variable. OK, do you know the global variables of other programming languages? Yes, in your favorite programming language. And I think most modern programming languages support it. A Global Variable in SQL Server is very similar to global variables in programming. As the name suggests it's global to all sessions of a particular instance. Oh. what is a session?

Let me provide an example. When you open a new tab in SQL Server Management Studio a new session is created.

So, again return to Global Variables. A Global Variable name starts with double @ symbols. For example:

@@CONNECTION

And those keywords are reserved keywords in SQL Server. You cannot use them for your own purposes.

Few very popular Global Variables in SQL Server

Let's have a glance at a few very popular Global Variables in SQL Server. I have tested those in my SQL Server R2 edition and an example result varies depending on your native setting of SQL Server.

Connection Variable

A CONNECTION variable keeps track of the number of connection attempts in the current session after login; see:

select GETDATE(),@@CONNECTIONS as 'Login Attempts'

CPU busy time

The CPU_BUSY Global Variable provides information on how long the system CPU has been busy after login.

SELECT @@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) AS 'CPU microseconds',
GETDATE() AS 'As of' ;

Server Name

When we create a connection string for a SQL Server, the server name is very essential for that, the Global Variable SERVERNAME contains the name of the current SQL Server.

select @@SERVERNAME as 'Server Name'

Version name of SQL Server

Sometimes, basically for resolving an issue, it is necessary to know the version name of the running SQL Server and for that we can just display the contents of the server Global Variable as in the following.

select @@VERSION as 'Version Name'

Default Language of SQL Server

It's not such a popular Global Variable but it's sometimes very important. Think when you don't know English (Chinese for me, Ha Ha..) and your System Administrator has set the default language to English. Then if you know where to alter that then it's a piece of cake for you. Yes, just set a new value for the @@LANGUAGE Global Variable.

select @@LANGUAGE as 'Language'

Count affected rows

@@ROWCOUNT provides the count of the affected rows. Yes, this variable keeps track of a number of the affected rows due to a recent query done by you. If you read a few articles regarding the best practices of Stored Procedures or functions then you can see people are:

select * from tblMessage
select @@ROWCOUNT as 'Rowcount'

Count transaction

Transactions are very important when executing multiple SQL statements and one operation is very much dependent on another. And in the case of a nested block, we implement a transaction to execute a SQL statement by a segment. Using the @@TRANCOUNT Global Variable we can get the depth of the transaction.

begin tran
      select @@TRANCOUNT as 'First Transaction'
            begin tran
                  select @@TRANCOUNT as 'Second Transaction'
            commit tran
commit tran

Lock Timeout in SQL Server

This lock timeout Global Variable determines the time for a thread to wait before attempting to access a locked resource. Get the total read and write information for a query as in the following:

set LOCK_TIMEOUT 100
select @@LOCK_TIMEOUT as 'Lock Timeout'

Total read and/or write information

Read and write information is basically needed for measuring the performance of a SQL query. A read or write operation may be performed on a disk or from a SQL Server cache. We can show read and write information like this:

select @@TOTAL_READ as 'Read' , @@TOTAL_WRITE as 'Write' ,GETDATE() as 'DateTime'

Find Service Name or Instance Name in SQL Server

A server or instance name is important at SQL Server login time. And to create a connection string in programming:

select @@SERVICENAME as 'Service Name'

Detect error using @@ERROR

The @@ERROR Global Variable is very important for printing an error code when an error occurs in the program flow. We can simply print the contents of the ERROR variable like select @@ERROR as in.

begin try
      RAISERROR('This is error',15,15)
end try
begin catch
      select @@ERROR
end catch

Output:- 5000, this is the error message code

Determining maximum connections in SQL Server

If you are a coder then you probably know how valuable one connection object is. And I hope you understand the concept of connection pooling. Anyway, we can specify the maximum number of connections for a SQL Server database. By default, it is 32767. If however, we are not interested in supporting that many connections then we can simply set our own value, as in:

select @@MAX_CONNECTIONS as 'Maximum connection'

We can get the Langage's current language ID of SQL Server

It's very easy to display the ID of the current language of your SQL Server; see:

SET LANGUAGE 'italian'
SELECT @@LANGID AS 'Language ID'

Show the last used timestamp value in the Database

If you know the basic concepts of the timestamp data type then at a glance you will understand this point. If not continue reading the next few lines. The Timestamp data type sets a unique value to our timestamp column. Whenever a read or write is performed the value is updated. So if we want to print the latest then use a Timestamp value as in the following query for your SQL Server database.

select @@DBTS

Getting the Process ID of the current session

The session concept of SQL Server is very similar to sessions in web applications. If we remember, the concept of a session is the amount of time between login and logout. So when we log in to SQL Server, a session is started. Now in each session, multiple processes may run.

At maintenance time or when locking occurs in SQL Server, it might be necessary to know the PROCESS id for killing the current process and releasing the deadlock. We can use the SPID Global Variable to get the id of the current process.

select @@SPID

Idle Time of SQL Server

The following example shows returning the number of milliseconds the SQL Server was idle between the start time and the current time. To avoid arithmetic overflow when converting the value to microseconds, the example converts one of the values to the float data type.

SELECT @@IDLE * CAST(@@TIMETICKS AS float) AS 'Idle microseconds',
GETDATE() AS 'as of'

Summary

This article taught us some popular Global Variables of a SQL Server Database.