Know Popular Global Variables in SQL Server

Dear reader, in my past few articles I have explaned 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 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 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 the information of 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 peace 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 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 transacton

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 Satement 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 determes 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 besically needed for measuring the performance of a SQL query. A read or write operation may be performed on 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 a 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

Determing 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 curent language of your SQL Server; see:

SET LANGUAGE 'italian'

SELECT @@LANGID AS 'Language ID'



Show last used time stamp value in Database

If you know the besic 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 Process ID of 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 logout. So when we login to SQL Server, a session is started. Now in each session multiple process 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 release 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 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'