Scope_Identity() - Output parameter or not?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Sql Server, Scope_Identity(), Ado.NET:

Which is better? Using an output parameter to return Scope_Identity through
ExecuteNonQuery(), or adding Select Scope_Identity() to the end of the
procedure or ad hoc SQL and using ExecuteScalar()?

Thanks.
 
Great! That's what I do, but I've seen examples here and there using output
parameters and wondered why.

Thanks

Mike


Eliyahu Goldin said:
The standard way is to run a batch
INSERT...;SELECT SCOPE_IDENTITY()...
in a single ExecuteScalar call.

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin


Mike said:
Sql Server, Scope_Identity(), Ado.NET:

Which is better? Using an output parameter to return Scope_Identity through
ExecuteNonQuery(), or adding Select Scope_Identity() to the end of the
procedure or ad hoc SQL and using ExecuteScalar()?

Thanks.
Click to expand...
Click to expand...
 
The standard way is to run a batch
INSERT...;SELECT SCOPE_IDENTITY()...
in a single ExecuteScalar call.
 
Mike,
I believe using a output parameter is more efficient.
If you do the select scope_identity() technique you are returning an
entire row of data as a DataSet, DataTable, DataReader.. There is
still overhead associated with it (more network traffic, bigger object
to instantiate)

An output parameter is certainly better.

Vince

Great! That's what I do, but I've seen examples here and there using output
parameters and wondered why.

Thanks

Mike

message

The standard way is to run a batch
INSERT...;SELECT SCOPE_IDENTITY()...
in a single ExecuteScalar call.
Click to expand...
--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
Click to expand...

- Show quoted text -
Click to expand...
 
My understanding of ExecuteScalar is that it returns 1 single value. The
docs say nothing about returning this in a datareader/dataset. In fact, the
docs say that any values other than the first column of the first row are
discarded.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

But thanks anyway.

Mike


Mike,
I believe using a output parameter is more efficient.
If you do the select scope_identity() technique you are returning an
entire row of data as a DataSet, DataTable, DataReader.. There is
still overhead associated with it (more network traffic, bigger object
to instantiate)

An output parameter is certainly better.

Vince

Great! That's what I do, but I've seen examples here and there using
output
parameters and wondered why.

Thanks

Mike

message

The standard way is to run a batch
INSERT...;SELECT SCOPE_IDENTITY()...
in a single ExecuteScalar call.
Click to expand...
--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
Click to expand...
Sql Server, Scope_Identity(), Ado.NET:
Click to expand...
Which is better? Using an output parameter to return Scope_Identity
through
ExecuteNonQuery(), or adding Select Scope_Identity() to the end of
the
procedure or ad hoc SQL and using ExecuteScalar()?
Click to expand...
Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
??
Why do you think select scope_identity() returns more than just a single
scalar value?

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin


Mike,
I believe using a output parameter is more efficient.
If you do the select scope_identity() technique you are returning an
entire row of data as a DataSet, DataTable, DataReader.. There is
still overhead associated with it (more network traffic, bigger object
to instantiate)

An output parameter is certainly better.

Vince

Great! That's what I do, but I've seen examples here and there using output
parameters and wondered why.

Thanks

Mike

message

The standard way is to run a batch
INSERT...;SELECT SCOPE_IDENTITY()...
in a single ExecuteScalar call.
Click to expand...
--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
Click to expand...
Sql Server, Scope_Identity(), Ado.NET:
Click to expand...
Which is better? Using an output parameter to return Scope_Identity
through
ExecuteNonQuery(), or adding Select Scope_Identity() to the end of the
procedure or ad hoc SQL and using ExecuteScalar()?
Click to expand...
Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
Eliyahu said:
??
Why do you think select scope_identity() returns more than just a single
scalar value?
Click to expand...

Because you are executing a select. It creates a result in the form of
data rows. The result is a single row containing a single field, but
it's still a result in the form of data rows.
 
Mike said:
My understanding of ExecuteScalar is that it returns 1 single value. The
docs say nothing about returning this in a datareader/dataset. In fact, the
docs say that any values other than the first column of the first row are
discarded.
Click to expand...

Yes, the method returns a single value, but the database query returns a
complete result to return that single value.
 
If you are using a stored procedure, you can return Scope_Identity() and it
will attach to the return parameter. Or, you can create an output parameter.

Using ExecuteScalar() gives you a firehose cursor with very few lines of
code, however. The speed of SELECT SCOPE_IDENTITY() is equivalent, and you
have fewer lines of code in the end than you do pulling a parameter.

In other words, do what you desire. You are not really going to see a major
difference either way you go.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************
 
Mike said:
But it appears to be faster (and there more efficient?) to use ExecuteScalar

http://codebetter.com/blogs/john.papa/archive/2005/04/10/61745.aspx

Mike
Click to expand...

With such a small difference as 3%, I would say that they seem to be
equally fast.

That test doesn't say anything at all about scalability or the use of
resources, though.

I don't have much substantial on this yet, but I would say that a
parameter is likely to use less resources than a data reader. A data
reader is built to handle data of very different sizes, and uses a
buffer of several kB for buffering the data, even if the data that you
actually get is only a few bytes.
 
Back
Top