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.


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

Query performance Call Center data (1d)

Looking for on Premises tool to read data from SQL logs and populate data warehouse (1d)

Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (1d)

Working with multiple WHERE statements (1d)

Create a new field value that shows a difference in value from 2 fields (3d)

Hierarchy wise Sales Targets (3d)

Get the MaxDate in results (5d)

Switch Statement within a SSRS SQL Expression (6d)

- Advertisement -