 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / June 2007Scope_Identity() - Output parameter or not?
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. The standard way is to run a batch INSERT...;SELECT SCOPE_IDENTITY()... in a single ExecuteScalar call.  SignatureEliyahu Goldin, Software Developer & Consultant Microsoft MVP [ASP.NET] http://msmvps.com/blogs/egoldin > Sql Server, Scope_Identity(), Ado.NET: > [quoted text clipped - 3 lines] > > Thanks. Great! That's what I do, but I've seen examples here and there using output parameters and wondered why. Thanks Mike > The standard way is to run a batch > INSERT...;SELECT SCOPE_IDENTITY()... [quoted text clipped - 8 lines] >> >> Thanks. 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. [quoted text clipped - 23 lines] > > - Show quoted text - ?? Why do you think select scope_identity() returns more than just a single scalar value? SignatureEliyahu Goldin, Software Developer & Consultant Microsoft MVP [ASP.NET] http://msmvps.com/blogs/egoldin > Mike, > I believe using a output parameter is more efficient. [quoted text clipped - 34 lines] > > > > - Show quoted text - > ?? > 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. SignatureGöran Andersson _____ http://www.guffa.com 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.execut escalar.aspx But thanks anyway. Mike > Mike, > I believe using a output parameter is more efficient. [quoted text clipped - 36 lines] >> >> - Show quoted text - > 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. SignatureGöran Andersson _____ http://www.guffa.com >> My understanding of ExecuteScalar is that it returns 1 single value. The >> docs say nothing about returning this in a datareader/dataset. In fact, [quoted text clipped - 3 lines] > 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.papa/archive/2005/04/10/61745.aspx 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, [quoted text clipped - 8 lines] > > 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. SignatureGöran Andersson _____ http://www.guffa.com 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. SignatureGregory A. Beamer MVP; MCP: +I, SE, SD, DBA http://gregorybeamer.spaces.live.com Co-author: Microsoft Expression Web Bible (upcoming) ************************************************ Think outside the box! ************************************************ > Sql Server, Scope_Identity(), Ado.NET: > [quoted text clipped - 3 lines] > > Thanks. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.