By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

Scope_Identity() - Output parameter or not?

P: n/a
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.
May 31 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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" <bl***@blank.blankwrote in message
news:uX**************@TK2MSFTNGP06.phx.gbl...
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.


May 31 '07 #2

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

Thanks

Mike
"Eliyahu Goldin" <RE**************************@mMvVpPsS.orgwrote in
message news:e%****************@TK2MSFTNGP03.phx.gbl...
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" <bl***@blank.blankwrote in message
news:uX**************@TK2MSFTNGP06.phx.gbl...
>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.



May 31 '07 #3

P: n/a
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

On May 31, 4:56 pm, "Mike" <b...@blank.blankwrote:
Great! That's what I do, but I've seen examples here and there using output
parameters and wondered why.

Thanks

Mike

"Eliyahu Goldin" <REMOVEALLCAPITALSeEgGoldD...@mMvVpPsS.orgwrote in
messagenews:e%****************@TK2MSFTNGP03.phx.gb l...
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" <b...@blank.blankwrote in message
news:uX**************@TK2MSFTNGP06.phx.gbl...
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.- Hide quoted text -

- Show quoted text -

Jun 1 '07 #4

P: n/a
??
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
<vi***********@gmail.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
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

On May 31, 4:56 pm, "Mike" <b...@blank.blankwrote:
Great! That's what I do, but I've seen examples here and there using
output
parameters and wondered why.

Thanks

Mike

"Eliyahu Goldin" <REMOVEALLCAPITALSeEgGoldD...@mMvVpPsS.orgwrote in
messagenews:e%****************@TK2MSFTNGP03.phx.gb l...
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" <b...@blank.blankwrote in message
>news:uX**************@TK2MSFTNGP06.phx.gbl...
>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.- Hide quoted text -
- Show quoted text -


Jun 1 '07 #5

P: n/a
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/lib...utescalar.aspx

But thanks anyway.

Mike
<vi***********@gmail.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
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

On May 31, 4:56 pm, "Mike" <b...@blank.blankwrote:
>Great! That's what I do, but I've seen examples here and there using
output
parameters and wondered why.

Thanks

Mike

"Eliyahu Goldin" <REMOVEALLCAPITALSeEgGoldD...@mMvVpPsS.orgwrote in
messagenews:e%****************@TK2MSFTNGP03.phx.g bl...
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" <b...@blank.blankwrote in message
news:uX**************@TK2MSFTNGP06.phx.gbl...
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.- Hide quoted text -

- Show quoted text -


Jun 1 '07 #6

P: n/a
Eliyahu Goldin wrote:
??
Why do you think select scope_identity() returns more than just a single
scalar value?
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.

--
Göran Andersson
_____
http://www.guffa.com
Jun 1 '07 #7

P: n/a
Mike wrote:
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.
Yes, the method returns a single value, but the database query returns a
complete result to return that single value.

--
Göran Andersson
_____
http://www.guffa.com
Jun 1 '07 #8

P: n/a

"Göran Andersson" <gu***@guffa.comwrote in message
news:el****************@TK2MSFTNGP05.phx.gbl...
Mike wrote:
>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.

Yes, the method returns a single value, but the database query returns a
complete result to return that single value.
But it appears to be faster (and there more efficient?) to use ExecuteScalar

http://codebetter.com/blogs/john.pap.../10/61745.aspx

Mike
Jun 1 '07 #9

P: n/a
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" <bl***@blank.blankwrote in message
news:uX**************@TK2MSFTNGP06.phx.gbl...
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.

Jun 1 '07 #10

P: n/a
Mike wrote:
"Göran Andersson" <gu***@guffa.comwrote in message
news:el****************@TK2MSFTNGP05.phx.gbl...
>Mike wrote:
>>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.
Yes, the method returns a single value, but the database query returns a
complete result to return that single value.

But it appears to be faster (and there more efficient?) to use ExecuteScalar

http://codebetter.com/blogs/john.pap.../10/61745.aspx

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

--
Göran Andersson
_____
http://www.guffa.com
Jun 2 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.