Overview
Recently, I had to revise a Stored Procedure with nearly 2.000 lines of code. The procedure was edited by three developers, one after another. It contained sub statements for about 30 tables with varying degrees of complexity. But essentially all Sub-Statements were designed to fulfill a similar task. While all contributions followed a more or less general style it was obvious that each developer coded his or her statements in a slightly different style. In fact, there were even deviations within the work of a single developer. Due to the different styles and coding techniques it was far from being easy to read and understand the statements for 30 tables. Different styles and coding techniques did have an impact on the readability and maintainability of the procedure.
- Did the developers use delimiters for object names?
- Was there a coding convention with respect to using small or capital letters for key words or functions, etc.?
- Were there inline comments available on each change of an entity?
- What about using temporary tables vs table variables?
- …
In the past 20 years of my work as a Consultant, I found it quite often difficult to achieve a common-sense concerning structuring and formatting code. With each developer having found his or her very individual coding style, it appears to be a hard piece of work for everyone agreeing on and applying a common style guide.
A review of another procedure (that was developed by a highly appreciated colleague) found that it was structured and formatted in a way that was very different from my preferences. Without thinking of the consequences, I reformatted the procedure while reviewing the code. The code itself was just perfect. The check-in process marked the procedure as being modified although there was no functional modification. Comparing the previous version with the reformatted version of course produced a bunch of differences between both versions, all due to reformatting the code and with no functional modification in place.
Any criticism was more than justified.
As SQL Server Management Studio does not offer support for a halfway reasonable structuring and formatting of SQL statements it is up to developers to defining a coding style guide and – of course – to adhering to this coding convention. With that a project team will achieve sufficient readability and maintainability of SQL Server artifacts.
This series of articles collects best practices with respect to structuring and formatting of SQL statements. It covers the following issues:
- Editor Options in SSMS
- Formatting SQL Statements (Part 1)
- Formatting SQL Statements (Part 2)
- Commenting a complex SQL Statements
- Structuring Stored Procedures an Functions, etc.
- Commenting Stored Procedures an Functions, etc.