SQL Best Practices
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 1
Lesson Agenda
Coding Standard/Convention
Write query effectively
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 2
Section 1
SQL CODING STANDARD
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 3
SQL Coding Standard
❖ Standardization has a positive impact on any business. It requires
consistent efforts and sheers the focus of the software development
team to meet quality goals.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 4
SQL Coding Standard
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 5
NAMING CONVENTION AND STYLES
1. Use UPPER CASE for all T-SQL constructs, excepts Types
Correct:
SELECT MAX([Salary]) FROM dbo.[EmployeeSalary]
Incorrect:
SELECT max([Salary]) from dbo.[EmployeeSalary]
2. Use lower case for all T-SQL Types and Usernames
Correct:
DECLARE @MaxValue int
Incorrect:
DECLARE @MaxValue INT
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 6
NAMING CONVENTION AND STYLES
3. Use Pascal casing for all UDO’s
Correct:
CREATE TABLE dbo.EmployeeSalary
EmployeeSalaryID INT
Incorrect:
CREATE TABLE dbo.Employeesalary
EmployeesalaryID int
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 7
NAMING CONVENTION AND STYLES
4. Avoid abbreviations and single character names
Correct:
DECLARE @RecordCount int
Incorrect:
DECLARE @Rc int
5. UDO naming must confer to the following regular expression ([a-zA-Z][a-
zA-Z0-9]).
Do not use any special or language dependent characters to name objects. Constraints can
use the underscore character.
Correct:
CREATE TABLE dbo.[EmployeeSalary]
Incorrect:
CREATE TABLE dbo.[Employee Salary]
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 8
NAMING CONVENTION AND STYLES
6. Use the following prefixes when naming objects
usp_: User stored procedures usp_: User stored procedures
ufn_: User defined functions DF_: Default constraints
view_: Views PK_: Primary Key constraints
IX_: Indexes FK_: Foreign Key constraints
CHK_: Check constraints
UNI_: Unique constraints
Correct:
CREATE PROCEDURE dbo.usp_EmployeeSelectAll
Incorrect:
CREATE PROCEDURE dbo.EmployeeSelectRetired –-without preffixed
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 9
NAMING CONVENTION AND STYLES
7. Name tables in the singular form
Correct:
CREATE TABLE dbo.[Employee]
Incorrect:
CREATE TABLE dbo.[Employees]
8. Tables that map one-to many, many-to-many relationships should be named by
concatenating the names of the tables in question, starting with the most central
table’s name.
Correct:
CREATE TABLE dbo.[EmployeeSalary]
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 10
Advantages of Implementing Coding Standards
1. Enhanced Efficiency
2. Risk of project failure is reduced
3. Minimal Complexity
4. Easy to Maintain
5. Bug Rectification
6. A Comprehensive Look
7. Cost-Efficient
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 11
SQL Coding Standard
SQL Comment
❖ Microsoft SQL Server supports two types of comments:
✓ Line comment:
✓ Block comment:
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 12
SQL Coding Standard
SQL Comment
❖ Always use comment to explain your code.
❖ Use natural/human language in comment to easy
understand.
❖ All comments should be same format.
❖ Break comment line to avoid horizontal scroll bar.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 13
SQL Coding Standard
Naming conventions
❖ Ensure the name is unique and does not exist as a reserved
keyword.
❖ Names must begin with a letter and may not end with an
underscore.
❖ Avoid abbreviations and if you have to use them make sure
they are commonly understood.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 14
SQL Coding Standard
Naming conventions
❖ Use a collective name or, a plural form for table names
❖ Never give a table the same name as one of its columns
and vice versa
❖ Always use the singular name for columns.
❖ Use table/column aliases for easier reading
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 15
SQL Coding Standard
Format code
❖ Always use UPPERCASE for the reserved keywords like
SELECT and WHERE.
❖ Break line to avoid horizontal scroll bar. It recommended
that start line with KEYWORD
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 16
Section 2
SQL BEST PRACTICES
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 17
SQL Best Practices
1. Avoid select *, use column names
❖ Use Column Names Instead of * in a SELECT Statement
❖ Original query:
× SELECT * FROM Students;
❖ Improved query:
✓ SELECT Name, ClassName FROM Students;
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 18
SQL Best Practices
2. Avoid including a HAVING clause in SELECT statements
❖ The HAVING clause is used to filter the rows after all the
rows are selected and it is used like a filter.
❖ It works by going through the final result table of the query
parsing out the rows that don’t meet the HAVING condition.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 19
SQL Best Practices
2. Avoid including a HAVING clause in SELECT statements
❖ Original query
❖ Improved query
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 20
SQL Best Practices
3. Eliminate Unnecessary DISTINCT Conditions
❖ The DISTINCT keyword in
the original query is
unnecessary because the
table_name contains the
primary key p.ID, which is
part of the result set.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 21
SQL Best Practices
3. Eliminate Unnecessary DISTINCT Conditions
❖ Original query
❖ Improved query
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 22
SQL Best Practices
4. Use UNION ALL instead of UNION
❖ The UNION ALL statement is faster than UNION
✓ UNION ALL statement does not consider duplicate s, and
✓ UNION statement does look for duplicates in a table while selection
of rows, whether or not they exist.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 23
SQL Best Practices
5. Avoid using OR in join conditions
❖ Original query
❖ Improved query
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 24
SQL Best Practices
6. Avoid any redundant mathematics
❖ Original query
❖ Improved query
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 25
SQL Best Practices
7. Consider using COUNT
❖ COUNT(1) gets converted into COUNT(*) by SQL Server,
so there is no difference between these. The 1 is a literal, so
a COUNT('whatever') is treated as equivalent.
❖ COUNT(column_name)
✓ If the column_name definition is NOT NULL, this gets converted to
COUNT(*).
✓ If the column_name definition allows NULLs, then SQL Server
needs to access the specific column to count the non-null values on
the column.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 26
SQL Best Practices
7. Consider using COUNT
❖ Never use COUNT(*)
✓ tt must read all columns and cause unnecessary reads.
❖ Always use COUNT(1)
✓ The primary key is the first column in the table and you want it to
read the clustered index.
❖ Always use COUNT(column_name)
✓ You can select which index it will scan.
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 27
SQL Best Practices
7. Consider using COUNT
❖ Original query
❖ Improved query
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 28
Summary
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 29
Thank you
09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy -
30
30
Internal Use
