SQLErudition.com

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