SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UDF as Computed Column Part 2


UDF as Computed Column Part 2

Author
Message
Dinesh Priyankara
Dinesh Priyankara
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 115
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumnpart2.asp
rajenshah
rajenshah
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
Dinesh
(As a side effect to the article), thanks for making me aware of the CREATE SCHEMA AUTHORIZATION command.
raj



bozo7
bozo7
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 1
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
noggin
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Pardon my ignorance but what is BOL?



Dinesh Priyankara
Dinesh Priyankara
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 115

Hi noggin,

BOL stand for Books Online

Dinesh


MCP MCSE MCSD MCDBA
currym
currym
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 57
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
Dinesh Priyankara
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 115

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
currym
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 57
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
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10204 Visits: 11958

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search