A good programming style is the basis for readable and comprehensive source code and thus contributes substantially to the maintainability of software in the broadest sense. Well formatted code, definition and adherence to naming conventions are just a few criteria.
The design (aesthetics) is subject to personal preferences of developers and therefor often the starting point of controversial discussions among software developers. However, integrated development environments such as Microsoft Visual Studio already support structured and formatted code very well with automatic source code formatting and code analysis features.
Unfortunately, SQL Server Management Studio (SSMS) proves to have a lack of comparable features when developing SQL artifacts. In addition to that – or possibly just because of that – well known and accepted programming styles are not applicable to SQL code. Developing SQL code follows a completely opposed paradigm compared to developing software code. While one single SQL statement may consist of hundreds of lines software commands tend to be rather short and compact. You use many commands for achieving a goal in software development. In SQL server it is highly recommended to write one single statement as SQL is based on the mathematical set theory.
With that it appears not be not easy to define a good SQL programming style.
But what exactly characterizes good SQL statements? And which role does aesthetic plays in good SQL statements. And what’s about functional aesthetic?
Basically, SQL code is good when it is readable and comprehensive.
Important criteria for this are a clean outline and structure as well as a sufficiently precise formatting and alignment of the code. If these conditions are met, power features of the SSMS editor such as the block-selection (Column Editor) and editing code in conjunction with the Search&Replace feature allows a very efficient editing code. In this sense, the demand for structured and formatted – in the broadest sense designed – SQL code results from the functional power features of the editor used.
Let’s take a look at how Wikipedia defines aesthetics. Accordingly, aesthetics is the study of perceptible beauty, of regularities and harmony in nature and art.
The following SQL statement is valid and can be executed without problems in the database AdventureWorksDW2017:
Although design is subject to personnel preferences, certainly the beauty, regularities, harmony and with that the meaningfulness of this statement can be called into question.
The same statement somewhat reformatted appears more aesthetic than the previous statement:
In any case, it is more readable and comprehensive. Individual components of the statement can be identified solely based on the position in the statement.
The concept of aesthetics may be far-fetched here in the technical sense. In my view, quite a lot of requirements for can be summarized by using this term.
The headline of this article focuses on the functionality of aesthetics. The article has less the function of SQL commands in mind but rather powerful editing features of the SSMS editor (but also other editors), which allow an efficient structural and formal design of a SQL statement. Two important power features here are the Column Editor and the Search&Replace feature, along with some affinity for using the keyboard instead of the mouse.
The column editor is such a powerful feature in many editors that it is incomprehensible to me how little this power feature is known or used among developers. Essentially, the column editor supports block wise selection and editing of texts, while by default, the selection of text is line-based.
For the line-based selection of texts the Shift key is required. The shortcut Shift+Cursor Right, for example, selects a word starting at the current cursor position to the right. The selection is done character by character and can span several lines:
For a block wise selection of a text, the Shift key in conjunction with the ALT key is required. In the following example, the shortcut ALT+Shift+Cursor Down can be used to extend the cursor over several lines, starting from the first occurrence of alias T01, and then using the shortcut ALT+Shift+Cursor Right to select a block covering all aliases.
The column editor thus allows the extension of the cursor over several lines and the selection of a block at any point in the editor. Text inputs are applied to all lines that the cursor spans. The following text was not duplicated by copy & paste but written after spanning the cursor over 5 lines exactly once in all 5 lines.
The column editor can also be used in conjunction with the mouse. Starting from the current cursor position, a rectangle can be drawn with the left mouse button while pressing ALT+Shift.
A basic requirement for an efficient use of the column editor is therefore in addition to a structured outline of SQL statements a precise indentation of the elements of an SQL statement.
Certainly, getting used to the column editor will take some time. But once you got used to it, you will not want to miss this feature anymore.
- Using the column editor works only in those areas of the editor that contain line breaks.
- You should use whitespaces instead of tabs for indentation, to allow an efficient use of the column editor. See Editor Options in SSMS.
- Block wise selection is supported by many editors and even Microsoft Word: SQL Server Management Studio, Microsoft Visual Studio, Notepad ++, etc.
- However, the implementation of this power feature can vary greatly from editor to editor.
- In SQL Server Management Studio and Microsoft Visual Studio in my opinion, the feature is implemented identically. Notepad++ also makes the column editor available via the shortcut ALT+Shift, but the implementation of that feature deviates from the implementation in the Microsoft products and (to my mind) is not as intuitive and comfortable as in the Microsoft products.
The Search&Replace feature is probably familiar to everyone and available in SSMS via the shortcut Ctrl+H. Usually used for replacing single text fragments, this feature proves to be another power feature for formatting SQL code.
An example… SQL Server supports as delimiter for object names either quotation marks ( ””) or square brackets (). Although delimiters are not mandatory, they increase the readability.
The following two SELECT statements differ only by delimiting the field names.
Inserting delimiters may appear quite challenging in larger statements. In particular, inserting the closing square brackets appears somewhat time consuming as the position for all brackets differs from line to line.
However, this task is done in a few moments.
The brackets are quickly inserted before and after all field names by using the column editor as shown in the following figure.
Using the Search&Replace feature, the closing square brackets can be easily indented to the left until there are no more spaces between the last character of the field names and the closing bracket. Simply replace the text <space>] by a single bracket ] until no more occurrences of <space>] can be found.
The more structured and formatted SQL code is, the more readable and maintainable it becomes. SSMS’s Column Editor and Search&Replace feature provide two powerful features that allow SQL code to be quickly and efficiently structured and formatted. The result may be regarded as aesthetic, but the aesthetic is rather incidental. The term aesthetics was used in this article as a provocative buzzword.