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:

--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.