In this article, improving of T-SQL code layout and presentation using various features and options in ApexSQL Complete will be described. Additionally, the Code structure feature will be examined, 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 SQL statements are changing 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 tab in the Options window:
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 the case of deeply nested parentheses.
To automatically insert the appropriate closing character, use the Closing characters section in the Options window:
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 allows users to 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 window, under the Inserts tab:
In the following example, the 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 a global alias for a table or a view is specified, 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 will assign temporary names to objects so they’re easier to work with when writing and reviewing code.
March 4, 2015
