[thelist] Using @@Identity

Joshua Olson joshua at waetech.com
Wed Aug 24 07:12:37 CDT 2005

> -----Original Message-----
> From: Ken Schaefer
> Sent: Tuesday, August 23, 2005 10:15 PM
> a) use Scope_Identity not @@IDENTITY.

Good advice.  Using the technique I suggested (which you recommend against
:-)), the change from @@identity to SCOPE_IDENTITY() is quite simple:

CREATE TRIGGER mytable_insert ON dbo.mytable

I was also thinking, just now, that you would also:

CREATE TRIGGER mytable_insert ON dbo.mytable
AS SELECT inserted.id AS id FROM inserted
-- assuming that id is the identity field in question!

> b) I would recommend against what Joshua has suggested. It 
> might make sense in the CF world

Which it does, since "typical" CF querying methods (using cfquery) are not
very rich in terms of exposing connection and parameter options.

> , but in ASP/ASP.NET you 
> should return an output parameter (which is very lightweight), 
> or use ExecuteScalar if you really want to return a recordset.
> ExecuteScalar is useful if you only want to return a recordset 
> with a single row and a single column. I would recommend 
> using an Output Parameter

This really makes sense.  Thank you for the brief overview.  I would like to
make one additional argument AGAINST the technique I suggested--it's not
very portable.  Some of the mainstream databases are just now beginning to
support triggers and stored procedures.  But, of the big 3 databases (IMO,
these are MS SQL Server, MySQL, and PostgreSQL), my technique only works on
MS SQL Server.  If you want to use a generalized system that works on all
databases, then you'll want to avoid things like Triggers and lean towards
Stored Procedures and Transactions.

Just my 2cents.

Joshua L. Olson
WAE Tech Inc.
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:

More information about the thelist mailing list