Overview
Most editors of well-known development environments including SQL Server Management Studio (SSMS) use monospaced fonts. With that, each character occupies the same amount of horizontal space. Texts can be easily indented and aligned when using monospaced fonts. An efficient feature for aligning and indenting text elements is the Tab-key. By using the Tab-key you can indent multiple lines at the same time.
But, using the Tab-key may result in problems…
- What is the indent size?
- What character(s) will be inserted in the indented space?
- What about developers using not the same editor?
- What happens if developers have configured different indent sizes?
In a multi-developer environment the definition of uniform editor settings, regardless of which editor is used, is a prerequisite for creating structured and well formatted code. But which settings are relevant for successful collaboration in a multi-developer project:
- Monospaced Font
- Don’t mix of Whitespaces and Tabs for indenting texts
- Replace the indent size with whitespaces
- Use the SSMS feature Indenting – Block
At the beginning of each project, all developers should agree on settings related to font, the editors to be used, the indent size and whether to replace tabs with whitespaces.
Monospaced Font
Since all integrated development environment editors use monospaced fonts by default, this point appears to be quite obvious. For completeness, it is to be mentioned here.
Non-monospaced fonts do occupy only that amount of horizontal space, that is needed for a correct representation of a character. The letter i occupies less space than the letter m. As a result you will face problems with an exact alignment of Text elements in multiple lines.
The field names [name] and [system_type_id] in the WHERE-Statement can not be aligned exactly when using the non-monospaced Font Arial. However with the monospaced font Consolas an exact indentation is no problem.
With that, an exact indentation requires the configuration of monospaced font.
Configuration in SSMS
The Font can be configured in SSMS as followed: Options| Environment| Fonts and Colors
Monospaced Fonts are marked with bold letters.
Don’t mix of Whitespaces and Tabs for indenting texts
Every developer has his or her own preferences regarding the formatting of SQL code. Some prefer a tab as a character for indenting text, some whitespaces, but most of them probably don’t care about that. If SQL procedures etc. will be edited by multiple developers, it is very likely that SQL artifacts do contain a creative mixture of both characters.
As long as the tab width or indent size is set to e.g. 3 characters by all developers, a SQL statement will probably have a consistent formatting. However, if a developer configures a tab width of three characters, and others tab width 4, a statement quickly becomes unreadable and therefor does not allow an efficient editing. The following statements illustrates the possible effects of mixing tabs and whitespaces in a text:
Obviously, the pipe symbols are aligned with a tab width of four characters. However, using other tab widths will quickly make it clear that the alignment gets lost.
In this example, the different indentations may appear minimal. For more complex statements, mixing tabs and whitespaces will result in unreadable code.
The reason of mixed whitespaces and tabs is usually a question of differing editor settings. Using different editors may also contribute to that problem.
Configuration in SSMS
The Indent Size can be configured in SSMS as followed: Options| Text-Editor| Transact-SQL | Tabstopps
Recommendation
- Use always spaces for indentation.
- The use of spaces allows a much more precise formatting.
- When using spaces, an identical representation of the statement is guaranteed, regardless of the editor used.
Tipp
- The column editor feature allows indenting several lines or text blocks at once. This feature can only be used efficiently with whitespaces as the character used for indentation.
- See Functional Design (Aesthetics) of SQL
Replace tabs by whitespace
If an editor supports an automatic replacement of tabs with whitespaces, SQL statements will always be displayed correctly, even if different editors are used by the developers.
Recommendation
- Replace tabs by whitespaces.
- Only whitespaces guarántee an accurate formatting of SQL code and ensure that the representation of a statement is always correct, regardless of the editor used.
Configuration in SSMS
The replacement of tabs with whitespaces can be configured in SSMS as followed: Options| Text-Editor| Transact-SQL | Tabstopps
Note
The size of the indentation is to be distinguished from the tab width. The tab size only specifies how many whitespaces a tab character will occupy. Pressing the Tab-key will indent a text by the indent size. For example, if the indent size is 10 and the tab size is 5, pressing the Tab-key inserts 2 tab characters – each representing 5 whitespaces.
Block Indentation
Obviously, using whitespaces as a means of structuring SQL statements will result in extra work. However, SSMS supports the developer here with an option of blockwise indentation. What’s it all about?
For example, if the cursor is behind the character * and the developer presses the Enter-key, not only will a new blank line be inserted, but the cursor will be automatically aligned with the first character of the previous line – the * character. Without the option of blockwise indentation, the cursor would always be positioned at position 1 of the new line.
Until SSMS 2014 (possibly SSMS 2016, too) the cursor was apparently indented with spaces/tabs at the block-indentation. However, these spaces and/or tabs were not inserted until the developer starts typing. In SSMS 2017, blanks and tabs are inserted during block indentation, depending on the settings.
Recommendation
- Use blockwise indentation to align subsequent new rows with the previous row.
Configuration in SSMS
The blockwise indentation can be configured in SSMS as followed: Options| Text-Editor| Transact-SQL | Tabstopps
