UDF as Computed Column Part 2

• Dinesh Priyankara

  SSCommitted

  Points: 1681

  More actions

  June 28, 2003 at 12:00 am

  #106769

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

• rajenshah

  SSC Enthusiast

  Points: 103

  More actions

  July 14, 2003 at 2:30 am

  #465259

  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

  More actions

  July 14, 2003 at 8:29 am

  #465342

  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

  More actions

  July 15, 2003 at 2:03 pm

  #465590

  Pardon my ignorance but what is BOL?

• Dinesh Priyankara

  SSCommitted

  Points: 1681

  More actions

  July 15, 2003 at 9:41 pm

  #465622

  Hi noggin,

  BOL stand for Books Online

  Dinesh

  MCP MCSE MCSD MCDBA

• currym

  SSC Eights!

  Points: 949

  More actions

  July 22, 2003 at 1:52 am

  #466327

  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

  More actions

  July 22, 2003 at 4:38 am

  #466351

  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: 949

  More actions

  July 23, 2003 at 3:16 am

  #466541

  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: 110209

  More actions

  July 14, 2004 at 4:28 pm

  #514565

  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.

  _____________
  Code for TallyGenerator