SQL Complete – Smart Code Completion and SQL Formatting

A week ago, I saw a notification about the release of dbForge SQL Complete 5.5. As a long-time user of this product, I have updated to the new version. A week later I decided to share my impressions of the new version of SQL Complete.

The first thing that immediately caught my attention is the integration with SSMS 2016, which has recently become my main working tool. The new version of SSMS now includes a huge number of innovations, including Live Query Statistics that won my heart…

In addition to the SSMS 2016 integration, the new syntax of SQL Server 2016 was supported in SQL Complete. Previously, when writing articles on the new syntax, I had to look in MSDN sometimes, and now there is no necessity:

SQL Complete - Smart Code Completion and SQL Formatting sqlcomplete01

Previously, when typing GROUP or ORDER, BY was added automatically, and it seemed like a small revolution. In the new version, I liked the further development of this idea – to prompt more syntactic structures:

SQL Complete - Smart Code Completion and SQL Formatting sqlcomplete02

Solarwinds

It is worth noting that phrase suggestion was implemented for DDL constructions:

SQL Complete - Smart Code Completion and SQL Formatting sqlcomplete03

Note also an improved suggestion inside the CROSS/OUTER APPLY constructions. I do not know for others, but these constructions are very useful for me. With their help, you can do UNPIVOT as shown in this picture:

SQL Complete - Smart Code Completion and SQL Formatting sqlcomplete04

As well as in individual cases influence the execution plan, forcing the optimizer to choose Index Seek when accessing data.

The context menu now includes the new command “Execute to cursor”, which came in handy a couple of times in practice:

SQL Complete - Smart Code Completion and SQL Formatting sqlcomplete05

What else can I say? Two new formatting profiles were added:

SQL Complete - Smart Code Completion and SQL Formatting sqlcomplete06

Now you can save time significantly without having to set up the formatting style for each construction from scratch. Here is an example of a badly formatted query:

select c.customername, o.orderdate,
ol.quantity
from sales.customers c join sales.orders o
on c.customerid = o.customerid join sales.orderlines
ol on o.orderid = ol.orderid
where c.isoncredithold = 0 and ol.unitprice > 0 order by o.orderdate desc,
ol.quantity

If you choose the first profile, after formatting you will get the following beauty:

SELECT c.CustomerName
     , o.OrderDate
     , ol.Quantity
FROM Sales.Customers c
    JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
    JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE c.IsOnCreditHold = 0
    AND ol.UnitPrice > 0
ORDER BY o.OrderDate DESC
       , ol.Quantity

If formatted with the second one, the result will be the following:

SELECT c.CustomerName,
       o.OrderDate,
       ol.Quantity
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE c.IsOnCreditHold = 0
    AND ol.UnitPrice > 0
ORDER BY o.OrderDate DESC,
         ol.Quantity

A small thing, but it makes a difference.

The rest of the improvements I saw “under the bonnet” – the speed when working with large scripts has increased (more than 1MB). This is especially true for those who often need to edit synchronization scripts of a schema or data.

Anyone who is interested in trying out the new version of SQL Complete can download it here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Setting Firewall Settings With Azure SQL Server VMs
Next Post
SQL SERVER – DMVs to Detect Performance Problems in SQL Server – Notes from the Field #135

Related Posts

7 Comments. Leave new

  • What are the differences in this tool and RedGate’s SQL Prompt?

    Reply
  • Ahh ! The much awaited feature of SQL is finally here. Thanks to Microsoft.

    Reply
  • Chris (a different one)
    August 5, 2016 1:16 pm

    Check the websites for functional differences, but for me you cannot ignore price. SQL Prompt is £225 whilst SQL Complete is $95 (I’ll let you do the conversion which ever way you need). Even better there is a free version of SQL Complete, that I have used for three years, and an active support forum where you can raise issues and feature requests.

    Reply
  • Andrey Langovoy
    August 5, 2016 3:44 pm

    Hi Chris,

    Both are good alternatives to the standard SSMS IntelliSense. They have similar functionality. The difference is that SQL Complete allows you to use formatting profiles that provide you with more flexibility while setting up formatting for T-SQL statements. But the main advantage is the price of course!

    Reply
  • I’ve no nothing but headaches with redgate sql search over the past year (using ssms2012 and above) – repeatedly crashes SSMS, and doens’t return results (even after clearing the cached files).

    Reply
  • I find that SQL Prompt works better, but it is not free. SQL Complete often crashed on me and produced less robust results. That said for a free tool it is good.

    Reply
  • Vinod Jacob (@vinodj)
    September 15, 2016 4:05 pm

    @Pinal Dave – have you had a look at the ApexSQL Complete
    I personally like the SQL Complete full version but ApexSQL Complete is free and wanted to know another SQL User’s review of the product – have not been able to find a good comparison between Devart SQL Complete and ApexSQL Complete

    Reply

Leave a Reply

Menu