Software Vendor Mistakes With SQL Server: Writing Functions That Already Exist

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!

Going Further

---

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.