We’ve covered working with legacy SQL in some depth thus far. Today’s topic is a reminder that you should modernize your code. There are some things which we needed to do when working with SQL Server 2000 that no longer make sense when working with SQL Server 2014. For example, learn how to use window functions (introduced in 2005 but really improved in 2012), the APPLY operator (introduced in 2005), the CONCAT function (introduced in 2012), TRY_CONVERT (introduced in 2012), FETCH/OFFSET (introduced in 2012), and so on. Each of these solves long-running problems (FETCH/OFFSET perhaps less than I’d like…) and as you maintain your old code, bring it up to date with modern practices.
JDate: A Case Study
Let me walk through a case study of where modernization really helps. An attribute named JDate has been my bete noir for the past year. JDate is a modified Julian date which is stored in our system as:
SELECT CONVERT(INTEGER, DATEADD(HH, -12, GETUTCDATE()));
The impetus for this was that, back when this code was created, there was no DATE type. Therefore, using this modified Julian date would save four bytes and allow for quick conversions like “JDate – 5” to indicate 5 days ago. I would argue that this was not a good design to begin with—making assumptions about how exactly a DATETIME value is stored is sketchy—but we’ll say that it was a reasonable decision at the time.
Today, however, this is not a reasonable design. There are several problems with using JDate:
- The value is incomprehensible. The JDate value for January 19, 2015 is 42021. Unlike a well-specified date type, this JDate is essentially a surrogate key, which means that people need at least one additional step to understand the actual date under question.
- This design leads to extra steps which have a performance cost. Our code base is riddled with conversions from real dates to JDates and from JDates back to real dates. This causes a minor database performance hit and a larger developer performance hit as it takes longer to read and understand the code.
- Since SQL Server 2008, we’ve had the DATE type. DATE types are 3 bytes, meaning that they take even less space than an INT (4 bytes) or a classic DATETIME (8 bytes). They also support DATEADD(), which means we can easily get DATEADD(DAY, -5, @SomeDate). We can even save DATEADD(DAY, -5, GETUTCDATE()) as a DATE type.
- “JDate” is used as an attribute on several tables, but it is as semantically meaningful as naming a date column “Date” or an integer field “Int.” We don’t really know which date this represents, and that makes understanding attributes on a table a little bit harder.
The solution here is to fix the design. This is a pretty large change, but it boils down to simplifying individual procedures and smaller processes. In my case, I’ve come up with a design document to populate various DATE columns (named appropriately) with the current JDate information, modify procedures to support the date columns instead of JDate, and update calling C# code to support the date columns. This isn’t an overnight change, but the net result is that I clean up a long-standing piece of technical debt and get an opportunity to refactor a substantial portion of the code base.
Modernization On The Small
Modernization doesn’t have to be a series of gigantic changes, however; sometimes, small changes can make a big difference in readability. Think about how we got the date without time in SQL Server prior to 2005:
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETUTCDATE()));
This code works, but it isn’t quite intuitive. Instead, with SQL Server 2008, we can do the following:
SELECT CAST(GETUTCDATE() AS DATE);
This code is easier to read and more intuitive. It’s a simple piece of code, but makes your life as a maintenance developer easier. Similarly, the CONCAT function simplifies building strings:
SELECT CONCAT ( 'This is a string. ', NULL, 12, ' ..... ', 31.884, 'Some other string. ' );
Old-style code would have looked like:
SELECT 'This is a string. ' + ISNULL(NULL, '') + CAST(12 AS VARCHAR(50)) + ' ..... ' + CAST(31.884 AS VARCHAR(50)) + 'Some other string. ';
And that’s the simpler version. Change all of these bits to variables and you’ll need to ISNULL() and CAST() even more.
To wrap up this section, I want to reiterate that you should keep your code base modern. Understand new features as they come out and see if they can make your life easier. This also means being willing to question your code’s assumptions and see if a particular design still makes sense. In the case of JDate, we have a design which no longer makes sense and which is worth changing. In other cases, it might be worth a comment explaining why you pick (or stick with) one particular design over something which may appear to be a better design at first glance.