How We Improved Database Code Quality

Back last year, when we first looked at what changed in SQL Server 2012 and what would have to be accomplished to migrate to this new version of the engine, one deprecated feature popped up: using table hints without the WITH keyword. We have this everywhere in our stored procedures code!

Fortunately, this is no final deprecation, but there are thousands of occurrences in the code, so it would be a huge work to do it in one time… better do it over time.

2^nd good fortune… all our database code is source-controlled using TFS (Team Foundation Server).

This lead to creating a check-in policy, allowing to check for any occurrence, and asking to remove it before checking in any code modification. We also added a lot of other validation checks, forcing the use SQL-92 join style (using the JOIN keyword), as well as forcing a common template, etc.

It was first deployed in the middle of 2012.

And we quickly saw the number of total table hints errors in the code decrease (below, split by database, for the main ones)…

We can see a slow decrease over just more than a year, the final drop being a specific task of finishing the cleaning of our main database.

But since there are a lot of other validation checks in our check-in policies, let’s look at the overall number of errors. The list of checks evolved over time and this is computed with previous releases and the current list of checks. The number of warnings was divided by 6 in the latest release!

If we look at these figures by type of warning, we even see some disappear…

We now have a cleaner code, easier to maintain, and with much less chances of getting errors when upgrading the SQL Server version, as we are not using any deprecated feature. We still have a few “soon to be deprecated”, but it is now under control.