UDF as Computed Column Part 2

  • Dinesh Priyankara

    SSCommitted

    Points: 1681

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumnpart2.asp

  • rajenshah

    SSC Enthusiast

    Points: 103

    Dinesh

    (As a side effect to the article), thanks for making me aware of the CREATE SCHEMA AUTHORIZATION command.

    raj

  • bozo7

    Ten Centuries

    Points: 1278

    I also was unaware of the CREATE SCHEMA AUTHORIZATION command thanks for taking a minute to explain it. It will truly come in handy in the future.

    Thanks for articles. I have been planning more UDFs since reading your first article and this second one confirms the path I was thinking down.

    Thanks,

    Ross

  • noggin

    SSC Rookie

    Points: 33

    Pardon my ignorance but what is BOL?

  • Dinesh Priyankara

    SSCommitted

    Points: 1681

    Hi noggin,

    BOL stand for Books Online

    Dinesh

    MCP MCSE MCSD MCDBA

  • currym

    SSC Eights!

    Points: 919

    Thanks for your work. I must have screwed something up but when I ran the 'Create Schema' script I received the following error:

    Server: Msg 156, Level 15, State 1, Line 4

    Incorrect syntax near the keyword 'NULL'.

  • Dinesh Priyankara

    SSCommitted

    Points: 1681

    Hi Currym,

    I have tested the script and had not found any error. Let me know the exact code you ran.

    Dinesh

    MCP MCSE MCSD MCDBA

  • currym

    SSC Eights!

    Points: 919

    Hmmmm... It was with the first script "Create Schema Authorization" but the entire exercise worked fine this morning. The fn_getDuration function is nice little headbanger.

    Thanks for your assistance.

  • Sergiy

    SSC Guru

    Points: 109670

    Sorry, Dinesh, you've got so many titles... Just like number of operands in your script.

    But what about performance of your scripts? It's good while it has only 4 rows to work out.

    What if you replace your function with this one?

    CREATE FUNCTION dbo.fn_getDuration (@STime datetime, @ETime datetime)

    RETURNS datetime

    AS

    BEGIN

     DECLARE @Duration Datetime

     SET @Duration = @ETime-@STime

     RETURN @Duration - floor(convert(real, @Duration))

    END

    First, it's more precise: it counts not only hours and minutes, but seconds and milliseconds as well. So, it's more usefull;

    Second, it works times faster;

    And third, it returns datetime value, so you can use it in criteria check for select from big table: WHERE dbo.fn_getDuration (..) > '05:00:00'

    Best regards,

    Sergiy.

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

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