Inline Table-Valued UDF vs. A View - Which to use?

Question

I recently created a couple inline table-valued UDF's and then referenced them in a couple views using Cross Apply in one case and Outer Apply in another case. After I got it working and tested, I thought it was a pretty cool use of UDFs and Cross/Outer Apply's. But then it occurred to me that I probably could have done the same thing using a view.

So, my question is this. For those of you who have done this sort of thing, how do you decide to use an ITV UDF vs. a View? I realize of course that the UDF can take parameters whereas a view cannot, but those UDF parameter values could be used in a Where clause when querying against the View.

Thanks.

Answer 1

Personally, it depends on the use case. I generally avoid views as I've had some bad experiences with them in my database-youth. I also like the UDF because of the ability to do some parameter sanitization inside the UDF. I can't do that as easily (or guarantee it happens every time) when using a view.

Here is my reference material on the subject: Scary DBA

Run the sample code - you can change the dataset size easily to match your dataset - and you can see what he's talking about.