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:
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:
It is worth noting that phrase suggestion was implemented for DDL constructions:
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:
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:
What else can I say? Two new formatting profiles were added:
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 &gt; 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)
What are the differences in this tool and RedGate’s SQL Prompt?
Ahh ! The much awaited feature of SQL is finally here. Thanks to Microsoft.
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.
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!
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).
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.
@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