ApexSQL Complete – How to improve SQL code layout and presentation

This video shows how to improve T-SQL code layout and presentation using various features and options in ApexSQL Complete.

In this video, we’ll examine how ApexSQL Complete highlights identifiers with brackets, how it manages closing characters, aliases, and column list.

Transcript

Hello, and welcome to this video presentation in which we’ll describes how to improve T-SQL code layout and presentation using various features and options in ApexSQL Complete.

As you can see, when you start typing, for the first time after you select the database in the SQL Edit, the hints list starts loading SQL database objects and metadata.

Cache loading speed depends on the total amount of meta data loaded. To speed up loading, exclude unwanted meta data categories e.g. Temporary tables from the Hints tab under the Options form:

The hint list can be managed from the ApexSQL Complete options, under the General tab:

Here you can set when the hint-list will be shown, how objects in the hint-list will be sorted and how many items will be shown in the hint-list at once.

As you can see, by default all keywords, are set to be in the upper case.

Under the Format keywords combo box, you can set how keywords will handled:

Highlighting the object from the hint-list, after a designated time interval e.g. 1 second, will result in the Object script box appearing showing the DDL script for the selected object:

To set the time interval, when will be the Object script will be shown, under the General tab of the Options menu, in the Display object script after box, set the time, or uncheck the Show object script check box to turn off this option entirely:

Now, let’s highlight some objects from the hint-list, and as you can see the Object script box will not appear.

Positioning the cursor over some object, will result in ApexSQL Complete displaying a detailed description of each database identifier used in the SQL statement that is currently being edited:

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

Just like the show object script option, you can be set the time interval when the tooltip for the object will be shown or turn off this option completely:

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. It’s easy to make a mistake, though, and omit a closing character, particularly in case of deeply nested parentheses. ApexSQL Complete has a solution for this

To automatically insert the appropriate closing character, use the Closing characters section in the Inserts section of the Options dialog:

With 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 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:

Additionally, you can set the alias to be first three letters of an object, to appear in upper case and always use AS keyword with alias:

Let’s use the same object and see the results:

In the following example, Pr is designated as an alias for the Production.Product 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, if auto-generate alias was selected:

When the Insert alias/object name in where clause of single object option is checked, under the Inserts tab, an alias or object names to the columns listed in the Where clause will be added, even when only one object is specified in the From clause:

Adding a WHERE clause will automatically list alias columns:

When enabled, the Enclose identifiers within square brackets option will automatically enclose an autocompleted column, object name, method, and other identifiers within brackets:

Thanks for watching. For more information, please visit www.apexsql.com

Note: Executed queries feature has been renamed to Query history