Discussion on:

Understand when to use user-defined functions in SQL Server

Good information.

You said, "I usually discourage using scalar-valued UDFs in a WHERE criteria statement" and that's exactly some of the things a developing database manager, like myself, needs to know. It's one of those things I find myself wondering about, but I just can't make the time to seek out the information. Reading an article like this over my morning "wake-up" cup of coffee is where I love to pick up these professional opinions. In fact, you stated that you will go into detail in a later article about this very topic and I look forward to reading that one.

Thanks again,

Gabe

Thanks for this great article !! I really like the advise you gave -- "I usually discourage using scalar-valued UDFs in a WHERE criteria statement because...". It explains poor performance of my SQL Procedures I have done in the past.

There are some details omited that leaves me wondering what exactly what you mean. For example, "I like to use table-valued UDFs when I need to use one or more values from different tables in a join operation where some type of calculation needs to be done and an aggregation returned." makes me wonder if one value is returned, then why do we need a table ? An example will be nice.

Thanks again for this insightful article!

Thanks for the article on UDFs. I also like to use UDFs when designing queries. It makes it easier to break complex queries into smaller chunks for debugging purposes. Later on, I will replace the UDFs if I need to improve the performance of the query. I have discovered that sometimes the UDF actually performs better.

So you are saying that if I use the a Scalar UDF ona variable or parameter in a where clause, it will not use an index? For example:

Select product_id, product_name from products where product_group_id = dbo.GetProductGroupIdFromName(@product_group_name)

will not use an index on product_group_id but if I did this:

Select @product_group_id = dbo.GetProductGroupIdFromName(@product_group_name)

Select product_id, product_name from products where product_group_id = @product_group_id)

will use the index on product_group_id?

Can you please give some more details on when it is more appropriate to use a View vs a table-valued UDF? I have a few table-valued UDFs created that now I feel maybe should have been views, but I can't figure out when it's better to use one vs the other.