The practise of Hard Coding – SQL Antipattern #002

I was troubleshooting a SQL Server connectivity issue , the developer insisted the connection string details were correct. The program was based around a series of sql scripts on different SQL Servers. The SQL scripts were executed by a program written in C#. After a while I realised the connection string was incorrect. Not only was it incorrect , but was hard coded.

Hard Coding is a classic SQL Antipattern. Hard Coding  works but if not managed correctly can cause problems in the future.  Read more on How to spot a SQL Antipattern

Embedding configuration data directly into a program works, but will lead to problems. Everytime a configuration data requires a change – the program source code needs to be changed.

A more effective of managing the program is to embed a method where the configuration method imports a value from an outside source. There are certain situations where developers are under pressure to deliver some code without a dynamic interface to an outside source of values. This should be viewed as a temporary workaround – and a long – term , more flexible approach should be developed

There are all sorts values developers hard code into sql code or code which executes sql code.

Here are some hard coding examples I’ve seen recently:

Constants – dates, file paths, default values,

Connection strings – server, username, password

Magic Numbers – status codes, return codes to application

Formatting – Based on data input , I’ve seen SQL code that will format and even push out HTML code to client app

Checking for hard coding should be part of any code review and go-live checklist and sign off before code is moved into Production. Developers and Programmers should be challenged about hard coding. It can be a grey area, sometimes there is no way forward apart from hard coding configurable values.

Read More

SQL Server - Stored Procedure Checklist - SQL Server DBA

How to spot a SQL Antipattern