Incorrect Row Counts Returned from SQL Trace and Extended Events when Using Scalar UDF - by Guy Glantser

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 786313 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 5/3/2013 1:04:06 PM
Access Restriction Public


When a query contains a call to a scalar user-defined function, the value of the "RowCount" field of the "BatchCompleted" and the "StatementCompleted" events returns incorrect results. The same results are returned in SQL Trace and in Extended Events (for the same corresponding events). If the query does not contain a call to a scalar UDF, then the results are correct.
Sign in to post a comment.
Posted by Microsoft on 7/17/2013 at 12:21 PM

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing these bugs are following:
1     Scenarios reported in the bug are not common enough.
2     A viable workaround is available.
Thanks again for reporting the product issue and continued support in improving our product.

Posted by Guy Glantser on 5/6/2013 at 5:32 AM
Hi Pituach,

Thanks for the comment.
Everything you wrote about functions is true, but it has nothing to do with the fact that SQL Trace and Extended Events report incorrect values in the "RowCount" field. This value is supposed to represent the number of rows returned from the query, which is a value that is perfectly known to the engine at execution time, regardless of the structure of the query and the use of any type of function whatsoever.
The various "RowCount" columns in the "sys.dm_exec_query_stats" view do return the correct values, even when scalar functions are involved. But SQL Trace and Extended Events fail to report the correct values. This is a bug.

Posted by pituach on 5/4/2013 at 11:24 AM
It's not a bug in my opinion because it's a direct result of normal behavior using standard function

Function In computer programming is a subroutine, a sequence of program instructions that perform a specific task, packaged as a unit (*). What you mention as a bug is actually the normal behavior of Function.

The scalar functions behave like the Multi-Statement Table-Valued Functions in this context. For the benefit and effective use Microsoft also provided us Inline Table-Valued Functions for the use in databases. Those functions are the unique ones and not the "regular function" like scalar functions. The question should be why Microsoft did not provide INLINE Scalar Function too.

* "packaged as a unit" have a consequences and it is not always the best performance solution, especially for databases need. The idea of INLIN is that the SQL server query engine can break the code inside the "function" and to combine it with the whole query. It is not "a unit" any more. The INLIN function are unique and do not behave like a function in this context. The final "execution plan" is building from the whole query (the outer code and the in function code). This "BUG" is a direct consequence from this behavior and the "lack of information" in the outer code about the inner functions code.