Formatter comments and features

[anatoliy.russ]

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

Code: Select all

    TBL.ID IN (30, 31, 32, 40,
               62, 70, 69, 71)

instead of

Code: Select all

    TBL.ID IN (30, 31, 32, 40, 62, 70, 69, 71)

2. Align the different sections of query (SELECT, FROM, WHERE) according to maximum indentation rather than just indenting for the length of the word.

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

instead of

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

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.

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

The error I get is

Code: Select all

SQL2.sql: Error (39,14): Unexpected symbol ')' (U+0029)
SQL2.sql: Error (31,14): Unexpected symbol ')' (U+0029)

[alexa]

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
...
2. Align the different sections of query (SELECT, FROM, WHERE) according to maximum indentation rather than just indenting for the length of the word.

We are planning to implement these features in one of the next product versions.

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.

Could you please let us know what version of dbForge and SQL Server you are using?

[anatoliy.russ]

Using version 3.8.220 of dbForge Studio Professional Edition on a SQL 2012 Server version 11.00.5058

[alexa]

We still have not been able to reproduce this issue with the data you provided on our side.

Will it be possible to record a video or provide us screenshots?

You can send a reply straight to our support system at supportATdevartDOTcom, so we will keep further correspondence with you on this issue via e-mail.