Scope_Identity() - Output parameter or not?

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

[Eliyahu Goldin]

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.

[Mike]

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.

[vincent.apesa]

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" <[e-mail]:

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

Thanks

Mike

"Eliyahu Goldin" <[e-mail] 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" <[e-mail] 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 -

[Eliyahu Goldin]

??
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" <[e-mail]:

Great! That's what I do, but I've seen examples here and there using

output

parameters and wondered why.

Thanks

Mike

"Eliyahu Goldin" <[e-mail] 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" <[e-mail] 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 -

[Mike]

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" <[e-mail]:

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

Thanks

Mike

"Eliyahu Goldin" <[e-mail] 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" <[e-mail] 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 -

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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

[Mike]

"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

[Cowboy \(Gregory A. Beamer\)]

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.

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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