SQL Formatter

As a beginner, one does not focus much on Formatting the queries while writing their code. This leads to a great confusion while referring the code in future. For a better readability, we beautify the SQL statements using SQL Formatter. In view of user’s perspective we format the SQL queries.

The formatting is done by applying indentations, spaces in between, and tabs between the statements.

For example, let’s consider a small SQL query for better understanding.

select * from employee ORDER BY e_salary;

This can be formatted as,

select 
   *
from
   ORDER BY e_salary;

Let’s look another complex example.

select * from employee where EXISTS (select * from employee, department where employee.id = department.id and department_name=’cse’ 

By formatting the above query,

(select 
   *
from
employee
where EXISTS
(
	select 
	   *
           from
           employee, department
	where
	employye.id = department.id
	and
	department_name = ‘cse’

In this way we can understand the code more efficiently.

By this formatting we can understand the query more easily. So, for this purpose we use formatting Tools.

SQL formatter is a beautifying tool for large SQL queries. We have many different tools for formatting SQL queries. Some of the tools are

1. Atom Beautify SQL Formatter
2. Poor Man’s T-SQL Formatter
3. Code Beautify SQL Formatter
4. Instant SQL Formatter

Now let’s have a look of how these tools work.

Atom Beautify SQL Formatter:

This tool is used for formatting all languages like sql, html, css etc... After installation, one can use command Palette to choose the editor for a specific language. Then we need to select the code which is to be formatted. And after the selection, by clicking on Beautify On Save we can get the results.

Poor Man’s T-SQL Formatter:

This tool helps us in handling complete multi-batch scripts, including object definition scripts such as stored procedures, triggers etc...

We should use this tool by launching SSMS. Then, from tools select format T-SQL code.

Code Beautify SQL Formatter:

This tool is Specially for SQL queries. To Beautify large SELECT statements, we use Code Beautify SQL Formatter. This tool indents the code and spreads the code into multiple rows.

Instant SQL Formatter:

This tool gives out a clean, well-structured script as an output from the unorganised script. This tool not only formats SQL code but also this tool can convert between SQL and C#, java.

Indentation of code:

In this block we have 3 stages of indentation.

1. None
2. Block
3. Smart

None:

If we select None, the cursor points to the starting point of the next line.

Block:

If we opt Block button, the cursor points over the previous line indentation in the next line.

Smart:

This option decides the indent styles automatically. This works on a query by default.

Rules for Formatting the SQL Code:

 Following standard rules for SQL formatting gives best results in greater efficiency.

Object Naming:

• One should use a singular name for table/column.
• Do not use SQL Keywords as Table name / column Name. If we use SQL keywords as the table name, then we should enclose the name with quotation marks.
• Do not start a table/column name with an underscore.
• Prefer using AS keyword mostly, as this increases the readability of the code.
• Do not give same name for both table and column to avoid confusion.
• Do not use special characters like  ‘&’, ‘$’, and ‘*’ .

Alignment:

• While formatting, every Keyword should start in the next line.

Ex:

select student_name , 
            student_ID
from student;

Comments:

One should not write too many comments to reduce the complexity.

If it is necessary to include comments, then prefer Multi-line comments.

Ex:

select  person_age, 
	person_DOB    
                 /*Age, DOB are the columns in Person Table -- Multi-Line Comments*/
from Person;

Indentation:

One needs to use a new line after each query. It looks good if one uses a new line after every comma also.

Queries of SELECT:

For writing a select statement, if the select statements consists of many columns, then every column should be written in a new line with a comma at the end of the line. comma should not be placed at the starting of next line.

Ex:

select person_ID,
	person_age,
	person_name,
	person_DOB
from Person;

Queries of WHERE:

If a query has a where statement, then it should be written in the next line without any indentation.

Ex:

select person_ID,
	  person_salary
from Person
where person_Name = “Dhruv”;

Queries of INSERT:

For inserting records into a table, we use INSERT keyword. Indentation should be followed.

Ex:

Insert into Person(person_ID, person_Name, person_age) values
	(1, ‘Dhruv’, 12),
	(2, ‘Ayaan’, 17);

Queries of UPDATE:

For Updating the values in the table, we use UPDATE keyword and SET keyword. So, every time when we update, the set keyword should start from a new line.

Ex:

Update Person
Set person_ID=101
Where person_name = ‘Dhruv’;

Long SQL Query:

Long SQL queries contains many sub queries in it. So, formatting is necessary in such cases. Every sub query must be intended on a new line.

Ex:

select person_ID,
	person_Name
	case
	    when  person_age < 18 then ‘below 18’
	    when person_age >= 18 then ’18 or above’
	end as Age
from Person;

Join Statements:

We Join two tables using INNER JOIN, OUTER JOIN, INNER JOIN. Along with these we use ON Operator.

So, ON operator should be written on a new-line.

Ex:

select e.ID,
	e.Salary,
	d.ID
from  Employee as e
JOIN Department as d
     ON e. Name = d.Name;

NOTE:All SQL Keywords like SELECT, WHERE, HAVING, ORDER BY, GROUP BY should be written in a new line.

Usage of SQL formatter tools:

If the SQL queries are small and less complex, we can indent them manually and understand the query.

But In case of large databases, we write complex queries. So, it becomes difficult for us to format/indent the queries manually.

Advantages of SQL Formatter:

• This tool transforms queries into more flexible way to read and understand them.
• When a query is formatted with required spaces, it becomes easy to debug the error and to fix them.
• It increases the efficiency of the Database Transactions.
• It helps in scaling the Database.