How to improve SQL code layout and presentation

In this article we’ll describe how to improve T-SQL code layout and presentation using various features and options in ApexSQL Complete. We’ll examine the Code structure feature, how ApexSQL Complete highlights identifiers with brackets, how it manages closing characters, aliases, and column list.

The Code structure feature

The Code structure displays the internal organization of T-SQL code in a script in a tree-like form on the left side of the SQL Server Management Studio’s query window.

To use the Code structure feature, in the ApexSQL Complete menu, select the Code structure option. Prior to displaying the Code structure, the Auto-complete option in the ApexSQL Complete menu must be enabled. Otherwise, a blank form will be shown.

The Code structure viewer elements are automatically updated (added, renamed, or removed) while the user is changing the SQL statements in the query window.

Highlighting identifiers with brackets

Another way to improve readability of a SQL script is to highlight identifiers with brackets. To enable this feature use the Enclose identifiers within square brackets [] option under the Inserts section in the ApexSQL Complete options dialog:

When enabled, this feature will automatically enclose an auto-completed column, object name, method, and other identifiers within brackets.

Clarifying identifiers with tooltips

The Tooltips for identifiers feature in ApexSQL Complete displays a detailed description of each database identifier used in the SQL statement that is currently being edited, while hovering over the identifiers themselves:

A tooltip will show not only the structure/child objects, but also their Extended properties when available.

Automatically inserting closing characters

Special characters such as single and double quotation marks, parenthesis and square brackets as well as multiline comments, are used in pairs in SQL statements, and omitting the closing charter will result in a syntax error. However, it’s easy to make a mistake and omit a closing character, particularly in case of deeply nested parentheses.

To automatically insert the appropriate closing character, use the Closing characters section in the ApexSQL Complete options dialog:

When the appropriate option is enabled, after typing any of the specified closing characters, ApexSQL Complete will automatically insert the corresponding closing character after the cursor:

Aliases and column list

Aliases let you assign temporary names to objects so they’re easier to work with when writing and reviewing code. ApexSQL Complete will add automatically created aliases to SQL tables and views if the Auto-generate aliases option is selected in the Options, under the Inserts tab:

In the following example we can see that alias edh is added for the HumanResources.EmployeeDepartmentHistory table:

SELECT * FROM HumanResources.EmployeeDepartmentHistory edh

In the following example, the adr alias is assigned to the Person.Address table. Aliases can be defined for all databases on all SQL Servers, all databases on a specific SQL Server, or just for a specific database. Aliases can be created in the Options window, under the Aliases tab:

If you specify a global alias for a table or a view, it will be inserted instead of the automatically created alias:

When the Insert alias/object name in WHERE clause of single object option is checked, under the Inserts tab in the Options window, the Auto-complete feature will add an alias or object names to the columns listed in the WHERE clause, even when only one object is specified in the FROM clause.

Adding a WHERE clause will automatically list alias columns:

Clear formatting guidelines and automatic implementation of formatting rules will ensure that all team members and anyone who inherits the code can easily read it, and aliases let you assign temporary names to objects so they’re easier to work with when writing and reviewing code.

March 4, 2015