Product Review: dbForge SQL Complete
If you are an intensive T-SQL writer, you might need an additional help As a T-SQL developer, one of the major obstacles that you will come across is determining the columns names for relevant tables. One of the major features, among many other valuable features, that this tool is equipped with is, finding the column names as when you as you type your SQL statements.
To start with, this is the version of the tool used for this product review.
Installation & configuration
Installation deviates from other tools mainly because of two reasons as stated below.
Since this tool is an add-in to either SQL Server Management Studio or Visual Studio, the installation will detect what versions that are installed on your computer. The user has the option of de-selecting unnecessary tools at the installation itself.
Very important installation feature in this tool is that there is a startup optimization. It uses general Ngen.exe. The Native Image Generator (Ngen.exe) is a tool that improves the performance of managed applications. (more details at https://docs.microsoft.com/en-us/dotnet/framework/tools/ngen-exe-native-image-generator). Ideally, default option is selected as shown in the below image, so that performance is improved.
There is no major configuration needed to operate this tool or add-in. In the options dialog, many options are given as shown below. This means that as a developer, you can customize this tool as well.
Main feature of this tool is to help developers when they use their famous SQL Server Management Studio (SSMS) . Please be noted that for this review, SQL Server Management Studio 17 was used.
Let us approach this with a typical case. Let us assume that you need to write a query by joining multiple tables.
First of all. SQLComplete will enable better code intelliSence than what you have with SSMS already. If are not sure what your schema is, with SQLComplete you can start typing the table name without specifying the schema as shown below.
The advantage of this option is that you will see the definition of the table before writing the query. This is very helpful so that developers will get an understanding of the table alone with index details. Also, it will provide you details with row count so that you can get an idea of how large this table is. As soon as you select the SalesOrderDetails tables, the relevant schema will be selected. Also, table alias will be provided automatically to ease the developer’s trouble.
As soon as you type the INNER JOIN, all the tables will be listed but importantly related tables are listed at the top which are the table most likely to be joined. This is identified by the Foreign key constraint.
This will be very helpful if you are to join with many tables as shown in the below image.
After selecting and joining the tables, next you need to select the columns as per your requirement. No emphasis is needed to explain the difficulties that developers will go through to select the necessary columns from the bunch of tables that you have joined. With the tool, it simply matters of typing the table alias and you will get the list of columns along with the data type and it’s length as shown below.
This can be extended to for WHERE clause, ORDER BY clause and GROUP BY clauses as well.
Formatting is something which worries many developers. By clicking the SQL formatting, single line code will be converted to a nicely formatted code as below.
SELECT SOD.OrderQty ,SOH.OrderDate ,SOD.UnitPrice FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID = SOH.SalesOrderID INNER JOIN Purchasing.ShipMethod AS SM ON SOH.ShipMethodID = SM.ShipMethodID INNER JOIN Sales.Customer AS C ON SOH.CustomerID = C.CustomerID
Also, the formatting can be customized according to your need.
Above option dialog will indicate how many options you have to customize formatting your code. In most of the other tools, you will have colour coding, tabs, and right margin. However, in SQLComplete you have the options of defining formatting for each syntax as seen in the above screen.
Even after you wrote the query, you have the option of viewing the table definition by clicking the table name in the query. Also, by right-clicking the table and selecting go to definition, you will be taken to the table definition into the SQL Server Management Studio which is again a very important option for developers. Also, if you need to modify tables alias, once it is changed, this tool takes the responsibility of changing the other references in the query without any issues.
CRUD generation – SQLComplete has the option of generating CRUD procedures. CRUD procedures mean, generating stored procedures for a table for Insert, Update, Delete and Select. If you are working with the data layer, it is important to generate crud stored procedures which this tool can help you out. Also, as shown in the below image, there are options for configuring CRUD procedures.
Tabs Color – how many times you have confused with your developer to production environments. Consequences can be very dangerous if you mistakenly delete production data thinking that you are deleting development data. To avoid this, SQLComplete provides you an option of having different table color so that users won’t be misled. Following is the way how it can be configured.
After this configuration, your SQL Server Management Studio tabs will take the color that you have configured leaving out possibilities of confusion.
Execution History – if developer wants to retrieve previously executed query, there is a separate window to get them as shown in the below image.
You have all the details about the query such as query, executed time, duration etc . if you want to get the previously executed query, developer needs simple click the to query and query will open in the SSMS query window.
From this tool, you have the option of retrieving the previously closed T-SQL documents which is again very valuable option for the developers.
Standard Edition of SQLComplte is 200$ which includes one-year support and upgrades is included by default. You can extend this period for up to three years with a significant discount. For the details for pricing please refer to https://www.devart.com/orderingfaq.html
SQLComplte is developer-centric tool which provides developers to enhance their development environment.