SQLTeam.com Logo

Return to Global Variables

Global Variables

Written by Chris Miller on 24 August 2000

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:

--BAD CODE! DON'T USE 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,

rocketscientist.