Using @@rowcount and other global variables
Part one of a two-part series of blogs

SQL comes complete with a few useful global variables, giving you information such as the row number of the record just inserted, or the number of rows affected by a query. Here's how to use them!

  1. Global Variables in SQL (this blog)
  2. Getting the number of rows affected - @@ROWCOUNT

This is part of our full online SQL tutorial blog.  However, you might also like to consider booking onto one of our SQL courses.

Posted by Andy Brown on 28 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Global Variables in SQL

The first thing to know about global variables in SQL is that they aren't variables (for these, read my separate blog).  Instead, they are useful bits of infomation made available by SQL Server, which you can use.

All global variables start with @@; all normal variables start with @.

The most useful global variables are the following:

Global variable What it holds / notes
@@IDENTITY Holds the  id number of the last record added.  I've covered the use of this in a separate blog (which explains that SCOPE_IDENTITY() is often a better alternative).
@@ROWCOUNT Holds the number of rows affected by an SQL command - covered in the next part of this blog.
@@ERROR The last error number generated by SQL (or 0 if there isn't one).  I'll be covering this in a separate blog on error-handling and error messages, and will link to this when it's complete.
@@SERVERNAME The name of the current SQL Server.
@@TRANCOUNT The number of currently open transactions (covered in a later separate blog).

You can see a full list of all of the global variables in SQL here.

Example of Global Variables

Here's an example of the use of the main global variables in SQL:

-- insert a film

INSERT INTO tblFilm (FilmName) VALUES ('E.T.')



@@SERVERNAME AS 'Server name',

@@ROWCOUNT AS 'Row count',

@@IDENTITY AS 'Id field value',

@@ERROR AS 'Error message',

@@TRANCOUNT AS 'Open transactions'

And here's what this could show:

Output from SQL

5 global variable values for this query.


Now let's look in more detail at one particular global variable: @@ROWCOUNT.

  1. Global Variables in SQL (this blog)
  2. Getting the number of rows affected - @@ROWCOUNT
This blog has 0 threads Add post