Global Variables

By Chris Miller on 24 August 2000 | Tags: Application Design

girish writes "What are @@rowcount and @@identity used for?"

These are two different "global variables". They are read-only and session dependent variables (they change from one session to the next) that contain information that is useful when you're writing scripts in SQL. @@RowCount returns the number of rows that were selected, inserted, or updated by the immediately preceeding statement. So, this:

select top 5 id, * from sysobjects
print @@RowCount

will print the number 5. But this:

select top 5 id, * from sysobjects
print 'hello'
print @@RowCount

will print the number 0. Why? Because the Print statement doesn't return rows.

After you perform an insert into a table with a column that is an identity, the global @@Identity variable is set to the value of the identity chosen for the row inserted. If multiple rows are inserted, the last value will be set. For example:

create table foo (
keyvalue int identity(1,1),
datavalue char(10)

If you run these statements:

insert into foo (datavalue) values ('hello')
print @@Identity

The first time this is run it will print 1, then it will print 2 if it is run again, and so on. A common mistake is to do something like this:

declare @Inserted_Key int
insert into foo (datavalue) values ('hello')
select @Inserted_Key = max(keyvalue) from foo

The problem with doing this is twofold. First of all, about the time that record 10,000 is inserted you'll start seeing some performance degradation. Second problem is that SQL Server is multi-user. You can't be guaranteed that your key value is the maximum value because someone else could have inserted into foo while you were. This would result in you getting the wrong key value, and all kinds of bad things happening. Icky. Instead, do something like this:

declare @Inserted_Key int
insert into foo (datavalue) values ('hello')
set @Inserted_Key = @@Identity

That will allow you to save off the identity that was inserted. Keep in mind that if you want to keep @@Identity, you really need to get it moved into a local variable immediately after the insert.

Hope that helps,


Related Articles

Application Locks (or Mutexes) in SQL Server 2005 (7 January 2008)

What I Wish Developers Knew About SQL Server (Presentation) (11 October 2007)

Multiple Active Result Sets (MARS) – Transactions and Debugging (17 June 2007)

Multiple Active Result Sets (MARS) (3 April 2007)

How SQL Server 2005 Enables Service-Oriented Database Architectures (8 September 2006)

Presentation: What I Wish Developers Knew About SQL Server (17 November 2005)

GeoCoding with SQL Server and Google (8 August 2005)

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

Other Recent Forum Posts

Excel blobs and BCP extract commands (23h)

How to match default colors available in Reporting Services 2017? (1d)

Syntax Error in query using PIVOT (3d)

Internal enclave error. Enclave raised an exception (major = 335, minor = 52) (4d)

Meta_Keys (5d)

Add Value to a column (6d)

Campo com valor hexadecimal ou criptografado (6d)

Recursive MS SQL Query (7d)

- Advertisement -