Formatter comments and features
Posted: Fri 19 Sep 2014 16:47
The following comments are regarding the dbForge MS SQL Studio tool that I use. Just to preface this, I did try to use the feature suggestion site to submit these requests but I can't seem to vote on or submit new requests.
There are quite a few great formatting options however they still generate code that's hard to read and it would be great if there could be some changes made.
One of the first issues is that the code formatter will not format two statements in the same window so if you have two SELECT statements in the same window you can format one at a time but not both.
Another issue is that the code formatter will not remove new lines in a SQL statement so if there are scattered newlines and blank lines in your statement, you have to remove them by hand.
SQL Pretty Printer at www.dpriver.com is what I used before and that is where I'm seeing a difference between dbForge. The Pretty Printer seems to generate more readable code due to the way it aligns different sections of code, below are a few examples.
(The following examples look better in a fixed width font)
1. Format a list of number in an IN clause so they aren't just one long string. This can become a problem for particularly long strings
instead of
2. Align the different sections of query (SELECT, FROM, WHERE) according to maximum indentation rather than just indenting for the length of the word.
instead of
Also the code formatter seems fickle when formatting code and I'm not certain the exact reason for the problem. For the query below, sometimes the formatter will not work and sometimes it will. I think it has to do when the formatter tries to format queries with pivot tables, there might be other reasons but that seems to be a common thread in the queries that have formatting errors.
The error I get is
There are quite a few great formatting options however they still generate code that's hard to read and it would be great if there could be some changes made.
One of the first issues is that the code formatter will not format two statements in the same window so if you have two SELECT statements in the same window you can format one at a time but not both.
Another issue is that the code formatter will not remove new lines in a SQL statement so if there are scattered newlines and blank lines in your statement, you have to remove them by hand.
SQL Pretty Printer at www.dpriver.com is what I used before and that is where I'm seeing a difference between dbForge. The Pretty Printer seems to generate more readable code due to the way it aligns different sections of code, below are a few examples.
(The following examples look better in a fixed width font)
1. Format a list of number in an IN clause so they aren't just one long string. This can become a problem for particularly long strings
Code: Select all
TBL.ID IN (30, 31, 32, 40,
62, 70, 69, 71)
Code: Select all
TBL.ID IN (30, 31, 32, 40, 62, 70, 69, 71)
Code: Select all
SELECT DISTINCT
TBL.ID
,TBL2.ID
FROM TBL
INNER JOIN TBL2
ON TBL.ID=TBL2.ID
WHERE TBL.ID!=0
AND TBL.YR=2014
Code: Select all
SELECT DISTINCT
tbl.Id
,tbl2.Id
FROM tbl
INNER JOIN tbl2
ON tbl.Id=tbl2.Id
WHERE tbl.Id!=0
AND tbl.Yr=2014
Code: Select all
SELECT
pivotTable.Pid
,pivotTable.[1] AS value1
,pivotTable.[21] AS value21
FROM
(SELECT
Tbl.Pid
,Tbl.Pt
,Tbl.Rs
FROM Tbl
WHERE Tbl.Id='sale'
AND Tbl.Rs=1
AND Tbl.Del=0
AND Tbl.Pt IN (1, 21)) AS pivotData
PIVOT (MAX(pivotData.Rs)
FOR pivotData.Pt IN ([1], [21])) AS pivotTable
Code: Select all
SQL2.sql: Error (39,14): Unexpected symbol ')' (U+0029)
SQL2.sql: Error (31,14): Unexpected symbol ')' (U+0029)