Tuesday, April 25, 2017

mySQL Workbench vs SQL Server Management Studio (SMSS) vs SQL Formatting Beautify SQL Code

This post will examine SQL Server Management Studio vs mySQL Workbench SQL Formatting vs "Beautify SQL Code" online

Beautification or SQL Formatting basically will syntactically colorize and format SQL to be multi-line so it is human readable.

There are a plethora of sites (see chart at bottom) online to beautify but only a few sites that work when you SQL is syntactically incorrect.

The true test of you beautifier is whether or not it can parse SQL that is broken, incomplete or has an systax error. Many online SQL Formatting sites and tools will fail.

There is original and best is Poor Man's T-SQL Formatter (org. Oct 2013) and a upstart https://sqlformat.org/ is also good (it's interface is just slightly slicker).

Let's take this sample incorrect SQL statement that is missing the s in select

elect p.shipid,   p.shipname,   c.desc ContactShipCOM,   a.desc SpacialCoordiantes from profile p left join contact c   on p.id = c.profileid   and c.ord = 1 left join address a   on p.id = a.profileid   and a.ord = 1 where p.shipid = 'Rocinante' and c.show='The Expanse'

Paste into https://sqlformat.org/ a SQL Formatting engine that works on incorrect SQL

Not exactly as shown, but very close.

SQL Server Management Studio (SSMS)

SQL Server 2016 Management Studio (SSMS) is a free download, get it here.

There is no built-in SQL Formatting for SSMS, but because of Microsoft's architecture to make it's tools extensible the community stepped-up and made a tool to fit this need. Poor Man's T-SQL Formatter has a 
SQL Server Management Studio (SSMS) and Visual Studio Add-in for many years now. The installer is same for both, but it will give you an option for the Visual Studio add-in during the install wizard steps.

incorrect SQL Statement, still formatted 

MySQL Workbench 

The latest MySQL Workbench 6.3 Community does have a built-in SQL Formatter but it does not work when the SQL is incorrect. Sadly, I have made a request for a fix for this in 2014, and still nothing has been done. Even though this is "open source" doesn't mean you get your request through the request queue and there is not other way to extend this product unlike SSMS. 

incorrect SQL Statement, NOT formatted 

So for the best SQL Editor and Formatter, I declare 
SQL Server Management Studio (SSMS) - Winner! 

No comments:

Post a Comment