Home Forums Article Discussions Article Discussions by Author Discuss content posted by Alan Burstein PatExclude8K RE: PatExclude8K

  • September 16, 2015 at 10:38 pm

    #1827291

    Thank you for checking it out.

    Kristen-173977 (9/16/2015)

    Very useful function, thank you.

    Hopefully not a stupid question?!! but is there is reason why this is a table-valued function rather than scalar?

    This is an excellent question.:-D I asked the same question a few years and the answer made me a much better developer.

    The short answer is performance. Inline Table Valued functions (iTVF) generally perform better than scalar user-defined functions. One reason (but certainly not the only reason) is that iTVFs can get a parallel query plan whereas scalar cannot.

    PatExclude8K and PatReplace8K[/url] are what some people call an inline scalar function. Check out this article: How to Make Scalar UDFs Run Faster[/url] it's where I learned the technique I'm talking about.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001