Post reply

Using Functions in a Select Statement - A good set based method?

  • April 30, 2010 at 5:30 pm

    #138728

    I'm not here to discuss a specific problem I'm having, just a conceptual one to improve my overall understanding of SQL.

    Let us say I am attempting to solve some problem where the number of records is large, so a good set based query is called for.

    My solution is the following:

    Create Function SomethingGreat

    --Takes in an ID and does something with it.

    SELECT ID, SOMETHINGGREAT(ID)

    FROM #TABLE

    This method only uses one SQL statement, but since the function is called on every line, does SQL generally handle this optimally, or does it iterate from each row in order to run the function?

    Basically is this category of solutions often a good method for speedy queries (assuming the functions themselves are optimally designed)?

  • April 30, 2010 at 6:20 pm

    #1160820

    huston.dunlap (4/30/2010)

    I'm not here to discuss a specific problem I'm having, just a conceptual one to improve my overall understanding of SQL.

    Let us say I am attempting to solve some problem where the number of records is large, so a good set based query is called for.

    My solution is the following:

    Create Function SomethingGreat

    --Takes in an ID and does something with it.

    SELECT ID, SOMETHINGGREAT(ID)

    FROM #TABLE

    This method only uses one SQL statement, but since the function is called on every line, does SQL generally handle this optimally, or does it iterate from each row in order to run the function?

    Basically is this category of solutions often a good method for speedy queries (assuming the functions themselves are optimally designed)?

    A TVF, that can be used in a where clause, would be good.

    A scalar function that gets called for every row, like you mentioned,... it depends. IMO, probably not.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply