Today we have a guest editorial as Steve is out of town.

Static code analysis, an analysis of the code before the build, is much loved by IT managers, because it gives you a quality ‘metric’. It is often loved by development team leaders, because it will warn you if something is going to break the build. It is often used as a measure of ‘technical debt’. There are several tools for doing static code analysis of SQL code. This may seem slightly odd, because a SQL Server database is quite unlike C# or Java code. A SQL Server database is, by contrast, a complex dynamic system that will, if necessary, decide on an execution plan that is quite different to the SQL query that is presented to it. SQL queries on the system catalog allows you to explore database objects, their relationships and attributes, in the live system. You build your database, and then you analyse it.

There are still plenty of objectives in a static analysis of the inert code that will build the database system. Although you can establish dependencies of routines such as functions or procedures from the live system, there is plenty of information that remains tantalisingly hidden: The use of unreliable code (such as IsNumeric); the presence of deprecated code where there is a better alternative; Excessive Cyclomatic complexity of code – I could go on, and have already done so. Dynamic code analysis via SQL misses out on all this information.

I use static code analysis, especially in Continuous Integration before a build from source control: it would seem foolish to pass up such a rich source of information. However, there is so much more, and different, information about code and design to be gained once the build is successful. Suddenly, dependencies are easy to establish. The table smells are easier to detect, and you can quickly find the ‘god-like’ views and functions that bring database to their knees. You can check for obvious sins such as duplicate indexes or missing documentation. Then, of course, you give the database a workload and use system views such as DMVs to make sure that the database is handling correctly.

I very much believe that a combination of static and dynamic code analysis can give you the best feel for the state of a database, particularly if you add in the results of  analysis of the ‘instrumentation’ the various integration tests. You have a lot of metrics, and a number of pointers to where to concentrate the effort to improve delivery. Basically, there are fewer surprises: and in development surprises are generally unpleasant.