Global Variables

By Chris Miller on 24 August 2000 | 2 Comments | 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.

Discuss this article: 2 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

SQL 2012 Linked server to MS Access DB (0 Replies)

Import Excel workbook/form into SQL Server (2 Replies)

where to put error handling in cursor? (2 Replies)

Trigger to handle multi - rows (0 Replies)

How to default drop down list to a specific value (3 Replies)

same stored procedures on similar DB delay (3 Replies)

how to handle null values with >= (3 Replies)

Output search criteria for multiple OR (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -