July 24, 2009 at 4:19 am
When using using stored procedures in SQL Server 2005 (or SQL2008), such as an Insert or to get the count of records in the table that do not return a recordset, which is preferable an Output parameter or a Return parameter?
I have always taken the view that as an output parameter passed to and from SQL Server Server from the calling Dot Net code will require one less block of memory to be created and disposed of, compared to a return parameter, the output method is preferable. To me, the coding for an output parameter seems cleaner and more flexible as one or more output parameters can be readily added.
Since SQL Server is so very fast and well optimised, producing a reliable benchmark comparing the two methods seems unlikely.
Besides personal preference, it there any guidance on this choice?
July 24, 2009 at 5:36 am
If you're returning rows of data, just use a result set. But if you're returning a single row with only a few columns, output parameters are faster. It requires less memory and fewer round trips to the server.
I haven't experimented with it yet (may try it today), but with 2008 and the table valued parameter, you might be able to get rows of data in an output parameter. Hmmm....
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 24, 2009 at 6:50 am
>If you're returning rows of data, just use a result set. But if you're returning a single row with only a few columns, output parameters are faster. It requires less memory and fewer round trips to the server.
Thank you. I was aware of the memory advantage but not of the reduction in round trips.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply