Jeff Moden (1/8/2012)
To add to the reasons not to use undocumented or, sometimes, even documented MS provided code...The Developers of SQL Server will occasionally make significant performance mistakes in their code just like the rest of us.
I absolutely agree that the msdb.dbo.sysjobschedules will never have enough rows in it to become a real performance concern. I am, however, concerned that someone may copy the code from the msdb.dbo.agent_datetime() function that MS provided and use it for much larger things. If someone were to use that function for converting million row inputs multiple times per day, you could be stressing your server just because of the way the function was built. To summarize and as Michael suggested, conversions to character based datatypes can take a real toll on performance.
Of course, such statements on performance of code aren't worth a hoot without a little evidence. 🙂
Here's the typical million row test table...
Heh... Michael must be slipping... his code is "only" 13 times faster. 😛
I hate to rain on results that show my code to be faster, but I think a fairer test would be if you had the function contents "in-line" or had my code in a function. The overhead of the function call can have a large impact on the results.
Demo Performance Penalty of User Defined Functions