How to Format SQL Queries – Best Practices

code formattingsql

Should I break SQL queries in different lines? For example in the project I am working on, we have a query that is taking 1600 columns! 1600 + tab chars. I wrote queries like this:

   "SELECT bla , bla2 , bla FROM bla " . 
     "WHERE bla=333 AND bla=2" . 
      "ORDER BY nfdfsd ...";

But they demanded me to put them in one line and said that my style is bad formatting. Why it is bad practice?

Best Answer

For source control reasons, we have linebreaks after every where clause, or comma. So your above turns into

SELECT bla 
     , bla2 
     , bla 
FROM   bla 
WHERE  bla=333 
  AND  bla=2
ORDER  BY nfdfsd
        , asdlfk;

(tabbing and alignment has no standard here, but commas are usually leading)

Still, makes no performance difference.

Related Solutions

Algorithm for formating SQL code

...is there any standard or a convention for how the SQL code should be formatted?

Standard, no. You can put an entire SQL statement on one line as far as an SQL parser is concerned.

Convention, sure there are lots. It depends whether you're trying to maximize changeability or minimize space. I've written SQL formatters for both cases.

I just used particular character combinations to tell me where to break the SQL statement.

Here's one example from a Java DB2 SQL formatter that I wrote. Another Java program generated the Java code. The SQL came directly from the SYSIBM tables.

protected void prepareIndex00Select(String codeFacl)
        throws SQLException {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT CODE_FACL, SEQ_FACL, FILLER_TOF ");
    sb.append("    , CODE_TOF, NAME_FACL, NAME_LENGTH ");
    sb.append("    , CODE_FMB, ID_NCIC_ORI, NBR_PRINTER_PREFIX ");
    sb.append("    , ID_PERSONNEL_OFC, COMPLEX_CODE ");
    sb.append("    , PHS_CODE, DESIG_FACL_GRP, IND_DESIG_AUTH ");
    sb.append("    , CODE_FACL_I_T, INTKEY_FACL, IND_CDM_SENTENCING ");
    sb.append("    , MAL_FEM_IND, DEL_AFTER, IND_INMATES ");
    sb.append("    , VALUE_SO_CPU_STD, VALUE_SO_CPU_DAY ");
    sb.append("    , CODE_CAT, VALUE_DCN, XIDBKEY ");
    sb.append("    , FACL_FK_REGN ");
    sb.append("  FROM ");
    sb.append(creator);
    sb.append(".FACL ");
    sb.append("  WHERE CODE_FACL = ? ");
    if (additionalSQL != null) sb.append(additionalSQL);

    psIndex00 = connection.prepareStatement(sb.toString());
    psIndex00.setString(1, codeFacl);

}   // End prepareIndex00Select method

Node.js – Preserving Pre-formatted Multi-Line Strings in Scripts

JavaScript now supports multi-line strings via the backtick sytax. For example:

const foo = `$query = <<<EOT
select
     field1
    ,field2
    ,field3
from tableName
where
    field1 = 123
EOT;`

References

Best Answer

Related Solutions

Algorithm for formating SQL code

Node.js – Preserving Pre-formatted Multi-Line Strings in Scripts

Related Topic