SQL formatting may not increase the functionality of the code but good and consistent formatting by the developers certainly affect the readability of the code and therefore makes it easier to maintain. Keyword and separator alignment, spaces and tabs provide opportunities for a lot of variations in code layout. A case-insensitive server/database give further flexibility in formatting with choosing upper or lower casing of the text.
Just as an experiment to see the effect of casing and object naming conventions on readability, I created this dummy stored procedure and formatted it in four different ways. The white-space layout, i.e. tabs, spaces, newline etc is same in all of them.
Which one do you prefer?
Do you have your own layout options that you would like to share?
Feel free to copy the script and share your formatted version in comments.
Version 1
-- underscore as word separator -- lowercase keywords create procedure dbo.sp_get_employee @employee_id int, @department_id int as begin select e.first_name, e.last_name, e.designation, e.salary, e.manager_first_name, e.manager_last_name, d.department_name from dbo.employee as e with (nolock) inner join dbo.department as d with (nolock) on e.department_id = d.department_id where employee_id = @employee_id and department_id = @department_id; return 0; end
Version 2
-- underscore as word separator -- UPPERCASE keywords CREATE PROCEDURE dbo.sp_get_employee @employee_id INT, @department_id INT AS BEGIN SELECT e.first_name, e.last_name, e.designation, e.salary, e.manager_first_name, e.manager_last_name, d.department_name FROM dbo.employee AS e WITH (NOLOCK) INNER JOIN dbo.department AS d WITH (NOLOCK) ON e.department_id = d.department_id WHERE employee_id = @employee_id AND department_id = @department_id; RETURN 0; END
Version 3
-- camel/pascal casing for objects names -- lowercase keywords create procedure dbo.spGetEmployee @EmployeeId int, @DepartmentId int as begin select e.FirstName, e.LastName, e.Designation, e.Salary, e.ManagerFirstName, e.ManagerLastName, d.DepartmentName from dbo.Employee as e with (nolock) inner join dbo.Department as d with (nolock) on e.DepartmentId = d.DepartmentId where EmployeeId = @EmployeeId and DepartmentId = @DepartmentId; return 0; end
Version 4
-- camel/pascal casing for objects names -- UPPERCASE keywords CREATE PROCEDURE dbo.spGetEmployee @EmployeeId INT, @DepartmentId INT AS BEGIN SELECT e.FirstName, e.LastName, e.Designation, e.Salary, e.ManagerFirstName, e.ManagerLastName, d.DepartmentName FROM dbo.Employee AS e WITH (NOLOCK) INNER JOIN dbo.Department AS d WITH (NOLOCK) ON e.DepartmentId = d.DepartmentId WHERE EmployeeId = @EmployeeId AND DepartmentId = @DepartmentId; RETURN 0; END
