SQL complete is one of the best time-saving tools for any DBA who works on database objects and data stored inside these objects using T-SQL. It’s in the job description to be very familiar with T-SQL both from a DDL and DML perspective. Data Definition Language requires writing statements like CREATE, ALTER, DROP, etc. and Data Manipulation Language applies to commands like SELECT, INSERT, UPDATE, etc.
Mastering querying SQL Server and writing T-SQL code effortlessly requires years of experience in the respective field. We all want to be more productive, get more things done during core hours, in a way that will make us a very efficient DBA. Wouldn’t it be nice to have a tool that will make all of the above easier? Fortunately, there is!
This article is a general review of one such SQL complete tool that enables DBAs to automatically complete SQL statements directly in SQL Server Management Studio and Visual Studio, improve productivity with snippets, keep track of all executed queries, auto-correct common typing errors, and much more.
Introduction
ApexSQL Complete is a SQL Server coding productivity tool with lots of features designed to provide a set of comprehensive utilities that can significantly improve productivity and speed up the development process.
Such tools are frequently overlooked by DBAs because of fear that installing any additional extensions to favorite IDE will slow things down. This is a widely held but false belief as add-ins, such as this SQL complete tool, are usually small utilities that don’t affect the performance of host applications on modern powerful machines.
With that in mind, let’s head over to SSMS with this add-in installed and see what it can do.
SQL complete statements
Code completion is the main feature of this add-in that allows auto-completion of SQL keywords, fragments, and even entire statements, filling in known and reserved words to save time and increase efficiency when writing queries.
When a new query editor is opened, as soon as typing is started e.g. a SELECT statement to retrieve rows and columns, the hint-list will appear offering valid members from the current context:
The add-in shows suggestions in the hint-list while typing, enabling users to quickly insert tables, columns, procedures, views, etc. also working with encrypted objects. The SQL complete is enabled by default and will suppress the native IntelliSense feature. But don’t worry. What it has to offer is much more.
Auto-completed keywords can be automatically formatted in UPPER, Proper, or lower case. The add-in allows users to view an object’s definition and description within the query editor. Another neat feature is its ability to automatically insert closing corresponding characters – forgetting e.g. the closing bracket, among others, is a very common mistake. The list of hint-list options goes on and on.
Customizable snippets
SQL snippets are code fragments that speed up coding by inserting frequently-used SQL constructs automatically.
The SQL complete add-in comes with more or less 230 built-in snippets that can be viewed from the Snippets library:
Start typing the name of any snippet from the library and it will appear in the hint-list. Select it to use it and SQL complete will enter T-SQL code in the query editor:
Snippets are also great for frequently running queries. DBAs tend to have a folder full of SQL scripts that they have to open up each time there’s a need. Why not save the frequently running query as a snippet and use it in just a few clicks? Here’s how to do it.
Creating a new snippet can be done either from the Options window or directly from the query editor. Highlight the code for a new snippet, right-click on it, and select Create snippet. In the newly opened window, give it a name and description (optional), and click OK to save it in the library:
Back in the query editor, as soon as the name of the newly created snipper is typed, it will pop up at the very top of the hint-list. When selected, SQL complete will enter the code as shown below:
SQL code auto-replacements
Text auto-replacements utility allows users to replace any text previously specified with the appropriate keyword, object name, or any kind of SQL code. Though it can be used for the same purpose as described in the previous example, auto-replacements are handy for fixing common typing errors and in that way speed up SQL coding.
Let’s see how it works. Can you spot a problem in the query below?
USE AdventureWorks2012; GO SELECT * FORM Production.Product ORDER BY Name ASC;
If you run this query, an error will be shown which states:
Incorrect syntax near ‘FORM’.
Oh, right. There’s a misspelled FROM as FORM. These are frequently found in keywords like SELECT, FROM, and WHERE, etc., or in column and table names.
An easy fix to overcome this problem is to go to ApexSQL main menu (Extensions menu in VS 2019) > ApexSQL Complete > Manage auto-replacements:
In the Auto-replacements window, click the New button to create a new item. For this particular example, specify that “form” should be replaced with “FROM” as shown below, and click OK in both windows to save changes:
Back in the query editor, next time there’s a typo and “form” is typed instead of “from”, SQL complete will automatically correct the mistyped keyword:
Log executed queries
Query history is a feature that logs information about executed queries. This allows users to browse a list of executed queries, search for specific ones, and re-use them rather than writing them again.
To go back in time, at least in the T-SQL world, navigate through SQL complete add-in main menu in the host application and pick Query history command. This will open a new window in which information about the date and time of the execution, user who executed a query, targeted database, duration, the status of execution, and duration can be found:
The T-SQL of executed queries can be searched through the search box at the top of the window. Only queries that meet search criteria will be shown, and the corresponding keyword will be highlighted in the T-SQL preview panel:
This window is always on top, but users can freely navigate out of it and perform other tasks. Doble-click on any query from the list executed in the past will place its T-SQL code in a new query editor so that it can be reused.
Connection tab coloring
Tab coloring is a feature that allows users to set query connection colors for individual instances of SQL Server down to the database level. Connections can be assigned to a specific environment thus making it easy to identify which connection a tab is currently using which can save time by eliminating the possibility of DBA making a mistake like executing heavy tasks in the production environment.
Setting up tab coloring is as easy as going to Tab coloring in Options window where server and database should be selected, and then assigned to an environment by clicking on the Add button as shown below:
Next time a new query is opened and the connection meets previously defined settings to one of the environments, SQL complete will color that query tab correspondingly making it easy to distinguish active environment:
Code structure viewer
Code structure utility helps users identify the construction of complex queries in the tree-like form of the code from an active query editor. It gives a high-level overview of T-SQL code which is useful for quick navigation and code understanding.
When enabled, the code structure window is shown on the left side of the query editor as shown below:
Moving the cursor thought statements in Code structure will quickly jump to different sections of a script in the query editor and highlight the corresponding code.
Database object search
Go to object is a utility for finding objects and highlighting them in Object Explorer. DBAs frequently perform database searches by querying system views to find specific objects and obtain additional information related to them. There’re some other ways, but here’s how to do it in ApexSQL Complete.
Select a database from Object Explorer to retrieve a list of objects from and navigate through SQL complete main menu to Go to objects command:
At first, the Go to object window will list all the objects in the database that SQL complete add-in has in its metadata AKA cache. As soon as a keyword is entered in the search field, results will be filtered and only matches will be shown:
Doble-click any object from the list to locate it in Object Explorer:
This can be achieved from the query editor as well. When working on a script, right-click an object in it and choose the Navigate to object option from the menu:
Query results search
Search results utility can save time when working on large Results set retrieved by a query. It allows users to search for data in one or even more results grids in case of multiple SELECT statements and highlight the results.
Once the Results set is populated with data, navigate through SQL complete main menu, and choose the Results search command. Type a keyword and click Highlight all option to find that particular keyword in the Results set data. Each time the Highlight all option is used, the number of found results is updated:
Practicing safe coding
Test mode is a feature that allows users to execute queries safely without affecting a database. Don’t waste time wrapping code around begin and rollback transactions. This SQL complete add-in will do that for you.
For example, let’s create a table by specifying columns from multiple sources using the example below:
SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, sp.Name AS [State/Province], a.PostalCode INTO dbo.EmployeeAddresses FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID JOIN Person.Address AS a ON bea.AddressID = a.AddressID JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID; GO
Now, before executing this code and selecting multiple columns from various employee-related and address-related tables, let’s check how many records will be created in the database.
Simply click on the Test mode option from the SSMS’s toolbar or navigate to it through the main menu and notice that SSMS’s status bar will go red:
This is an indicator that any executed code will not make actual changes to a database’s structure. However, when a query is executed in test mode, it will give us information on how many rows will be affected:
Next, if the result is satisfactory and there’s nothing odd about the outcome of a query, simply turn off the test mode, and rerun the query for the changes to go through. Pretty neat. Right?
SSMS and VS integration
The add-in integrated simultaneously to both SQL Server Management Studio and Visual Studio.
Simply run the executable installer and in the host integration step, select SSMS and/or VS versions to integrate the SQL complete add-in into and click the Install button:
- For detailed information about the installation options, see How to install ApexSQL add-ins and integrate into host environments e.g. SSMS, Visual Studio.
Extra features
The add-in has a ton of features and going through each of them would take time. Nevertheless, some of the honorable mentions are listed below in the form of knowledgebase articles:
These might not be directly connected to boosting productivity but will definitely come in handy for use in some of the tasks DBAs are faced with.
Conclusion
Seeing the above, it’s safe to say that this SQL complete add-in includes a set of productivity utilities that belongs in the toolbox of any DBA who works with Microsoft SQL Server using either SQL Server Management Studio, Visual Studio or even both. Code completion and keywords formatting, snippet insertion, auto-replacements, etc. are just some of the features that contribute to higher efficiency in the development process.
We hope you found this article helpful. Happy coding!
August 26, 2020
