Suppose i have a scalar value udf that returns 1 or 0, udf_checkCondition( string condition). I am using the udf in the following query's where clause, along with other conditions. How it will be executed and how many times the UDF will be called. select * from table1 where c1 = @c1 and c2 = @c3 and udf_checkCondition(c3) = 1 Suppose the table has 1000 records., Applying first two conditions, only 2 or 3 rows matches. Will the UDF be called for all rows or only filtered rows. please help me to decide on using UDF?
Welcome to the forum! Since you have AND conditions, all 3 must be met for a row to be returned. So, I would expect SQL Server to narrow down to these 2 -3 rows before applying as the 3rd the UDF. However this is not guaranteed: http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596401.aspx