Reinventing The Sequel
In my time consulting, I’ve seen dbo-prefixed functions that have internal-function names on a number of occasions, acting like a wrapper to the existing functions that come with SQL Server.
While those are fairly ridiculous, and easy to point out the ridiculousness of, there are some other functions that I see pretty often that are maybe a little less obvious. I’m not talking about functions that have been added more recently that might not be supported by all customer installations.
All Supported Versions
One of the more popular versions of this malady that I see looks something like this:
CREATE OR ALTER FUNCTION dbo.ufn_FmtDt ( @d datetime ) RETURNS char(11) AS BEGIN RETURN CONVERT(char(11), @d) END; GO
There are two big reasons this is bad:
- SQL Server doesn’t store dates as strings, at all, ever
- You can generate the same internal representation by converting to a date
You gain nothing by encapsulating code like this, at least not since SQL Server 2000 or so.
Do It Again
Using some lessons we learned from earlier in this series, we can keep reasonable up to date with SQL Server’s progress by rewriting the function to something like this:
CREATE OR ALTER FUNCTION dbo.ifn_FmtDt ( @d datetime ) RETURNS TABLE AS RETURN SELECT d = CONVERT(date, @d); GO
And now when you need to chop the times off all those dates in your reporting queries, they won’t be artificially held back.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Software Vendor Mistakes With SQL Server: Using Date Functions On Columns Or Local Variables
- Software Vendor Mistakes With SQL Server: Using Functions In Join Or Where Clauses
- Software Vendor Mistakes With SQL Server: Not Using Inline Table Valued Functions
- Software Vendor Mistakes With SQL Server: Multi Statement Table Valued Functions