{"id":143,"date":"2018-05-16T23:59:29","date_gmt":"2018-05-16T21:59:29","guid":{"rendered":"https:\/\/sql.marcus-belz.de\/?p=143"},"modified":"2020-04-22T01:50:02","modified_gmt":"2020-04-21T23:50:02","slug":"functional-design-aesthetics-of-sql","status":"publish","type":"post","link":"https:\/\/sql.marcus-belz.de\/?p=143","title":{"rendered":"Functional Design (Aesthetics) of SQL"},"content":{"rendered":"

Dieser Artikel in Deutsch…<\/a><\/p>\n

Overview<\/h2>\n

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.<\/p>\n

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.<\/p>\n

Unfortunately, SQL Server Management Studio (SSMS) proves to have a lack of comparable features when developing SQL artifacts. In addition to that \u2013 or possibly just because of that \u2013 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.<\/p>\n

With that it appears not be not easy to define a good SQL programming style.<\/p>\n

But what exactly characterizes good SQL statements? And which role does aesthetic plays in good SQL statements. And what\u2019s about functional aesthetic?<\/p>\n

Basically, SQL code is good when it is readable and comprehensive.<\/p>\n

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<\/strong> feature allows a very efficient editing code. In this sense, the demand for structured and formatted \u2013 in the broadest sense designed \u2013 SQL code results from the functional power features of the editor used.<\/p>\n

Design (Aesthetics)<\/h2>\n

Let\u2019s take a look at how Wikipedia defines aesthetics<\/a>. Accordingly, aesthetics is the study of perceptible beauty, of regularities and harmony in nature and art.<\/p>\n

The following SQL statement is valid and can be executed without problems in the database AdventureWorksDW2017<\/strong>:<\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

The same statement somewhat reformatted appears more aesthetic than the previous statement:<\/p>\n

\"\" <\/a><\/p>\n

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.<\/p>\n

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.<\/p>\n

Functionality<\/h2>\n

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<\/strong> and the Search&Replace<\/strong> feature, along with some affinity for using the keyboard instead of the mouse.<\/p>\n

Column Editor<\/h3>\n

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.<\/p>\n

For the line-based selection of texts the Shift key<\/strong> is required. The shortcut Shift+Cursor Right<\/strong>, 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:<\/p>\n

\"\"<\/a><\/p>\n

For a block wise selection of a text, the Shift key<\/strong> in conjunction with the ALT<\/strong> key<\/strong> is required. In the following example, the shortcut ALT+Shift+Cursor Down<\/strong> can be used to extend the cursor over several lines, starting from the first occurrence of alias T01<\/strong>, and then using the shortcut ALT+Shift+Cursor Right<\/strong> to select a block covering all aliases.<\/p>\n

\"\"<\/a><\/p>\n

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<\/strong> but written after spanning the cursor over 5 lines exactly once in all 5 lines.<\/p>\n

\"\"<\/a><\/p>\n

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<\/strong>.<\/p>\n

\"\"<\/a>   \"\"<\/a><\/p>\n

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.<\/p>\n

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.<\/p>\n

Note<\/strong><\/h3>\n