none
User Defined Table function vs. a regular view RRS feed

  • Question

  • Hi friends!

    From what I can see, a user defined table function can pretty much replace a view in Sql server 2005. I have really comed to like the table functions and I have found my self using views more and more rarely.

     

    However, I'm a bit unsure about the difference regarding performance etc. I have tried to google for an answer but didn't find much... I'd be very pleased if someone could give a short brief of what to think about when choosing between a UDTF and a view, or show me a page where I can read about it.

     

    Regards

    Andreas

    Monday, June 9, 2008 7:04 AM

Answers

  • Hi Andreas,

     

    I believe this is one of those areas where "it depends". I've seen performance improvements from moving from views to UDFs, and also from moving from UDFs to views.  The main difference is that functions suffer from the overhead of a function call, creating the resultset for the function, and then applying it to the query.  Views, on the other hand generally (unless you use NOEXPAND to force an index) simply expand the view name in the query with the definition of the view, and the query optimiser figures out how to optimise the query the best way.  It's also possible to create an index on a view.

     

    It's always easy to convert a view to a UDF, but the other way can be a little more involved, depending on the complexity of the UDF.  I recommend trying out your queries as both UDFs and views, and see which gives better performance.

     

    I would tend to lean towards views over UDFs, but ultimately, it comes to how many times the UDF/view is called, how many rows are involved, how complicated the view could be to expand and optimise.

    Monday, June 9, 2008 8:52 AM
    Answerer
  • One advantage that views have over functions is that views can be indexed whereas table functions cannot.  However, INLINE table functions do not suffer from many of the same performance problems that multi-line table value functions suffer from.  Notice in this post that Umachandar lists inline functions ahead of views in terms of performance.  Here is another post in which Adam Machanic and Umachandar discuss functions and views in terms of performance.

     

    EDIT:

     

    Here are some other posts in which Umachandar discusses the issue of performance of views versus performance of UDFs:

     

       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=199179&SiteID=1
       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97138&SiteID=1
       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74441&SiteID=1

    Monday, June 9, 2008 11:54 AM
    Moderator

All replies

  • Hi Andreas,

     

    I believe this is one of those areas where "it depends". I've seen performance improvements from moving from views to UDFs, and also from moving from UDFs to views.  The main difference is that functions suffer from the overhead of a function call, creating the resultset for the function, and then applying it to the query.  Views, on the other hand generally (unless you use NOEXPAND to force an index) simply expand the view name in the query with the definition of the view, and the query optimiser figures out how to optimise the query the best way.  It's also possible to create an index on a view.

     

    It's always easy to convert a view to a UDF, but the other way can be a little more involved, depending on the complexity of the UDF.  I recommend trying out your queries as both UDFs and views, and see which gives better performance.

     

    I would tend to lean towards views over UDFs, but ultimately, it comes to how many times the UDF/view is called, how many rows are involved, how complicated the view could be to expand and optimise.

    Monday, June 9, 2008 8:52 AM
    Answerer
  • One advantage that views have over functions is that views can be indexed whereas table functions cannot.  However, INLINE table functions do not suffer from many of the same performance problems that multi-line table value functions suffer from.  Notice in this post that Umachandar lists inline functions ahead of views in terms of performance.  Here is another post in which Adam Machanic and Umachandar discuss functions and views in terms of performance.

     

    EDIT:

     

    Here are some other posts in which Umachandar discusses the issue of performance of views versus performance of UDFs:

     

       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=199179&SiteID=1
       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97138&SiteID=1
       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74441&SiteID=1

    Monday, June 9, 2008 11:54 AM
    Moderator