SQL Query Writing Guidelines

About This Presentation

Title:

SQL Query Writing Guidelines

Description:

DBA would set the database permission not to allow SELECT INTO ... Always TRIM the variable, if a DATE is converted into a CHARACTER data type. ... – PowerPoint PPT presentation

Number of Views:244

Avg rating:3.0/5.0

Slides: 51

Provided by: Hem64

Category:

more less

Transcript and Presenter's Notes

Title: SQL Query Writing Guidelines

1
SQL Query Writing Guidelines
2

Each Procedure should have header information
File Name
Version
Procedure Name
Purpose
Author
Date
Purpose
Procedure should be easily readable.

Code Indentation should be maintained properly
(Use tab space for indentation).
e.g.,
BEGIN
SELECT name into name_tmp
FROM emp
WHERE empcode
empcode_tmp
AND sal
10000
END

Use simple logic in procedures
When a complex logic is used, explain the logic
with proper comments or pseudo code.
When modifications are done in the procedure,
modification details should be added. Add these
details just above the code being modified
/ Modified by ltName of the persongt /
/ Modified Date lt Date of modification gt /
/ Bug No ltNo of the bug for which the
modification is donegt /
/ Bug Description ltShort description of the
buggt /

Length of the database object names, variable
names should not exceed 25 characters.
Table Name, Stored Procedure name, View name,
User defined data type name etc.
Declaration of all the local variables should be
done at the beginning of the procedure.
Always use Standard UDDs for declaring local
variables
Procedure/ Function parameters UDD should match
with that of the method parameters.

Names of local variables should be meaningful as
per the context.
Reserved words like ROLE, DATE, DELETE should not
be used for object names and variable names.
variables should not start with a number or a
special character like _at_2position
LTRIM, RTRIM should be done for all parameters
and variables
LTRIM, RTRIM, UPPER should not be used as part of
the WHERE clause of a query
LTRIM / RTRIM / UPPER should not be used for date
/ integer fields
LTRIM / RTRIM / UPPER should not be used against
a table column in a where clause of a SELECT
query
Convert the table column values to UPPER or TRIM
the value before storing the same in the table
before INSERT

CREATE PROCEDURE proc1
_at_param1 UDD_EMPLOYEE_NAME,
_at_param2 UDD_DEPT_NAME
AS
BEGIN
SELECT emp_no, emp_name, dept_name
FROM employee e, department d
WHERE e.dept d.dept
AND e.employee_name UPPER(_at_param1)
AND d.dept_name UPPER(_at_param2)
/ Above two lines are not the recommended way
of using functions /
/ The same holds good for RTRIM, LTRIM etc. /
/ Optimizer may not use the index /
END

CREATE PROCEDURE proc1
_at_param1 UDD_EMPLOYEE_NAME,
_at_param2 UDD_DEPT_NAME
AS
BEGIN
SELECT _at_param1 UPPER(_at_param1)
SELECT _at_param2 UPPER(_at_param2)
SELECT emp_no, emp_name, dept_name
FROM employee e, department d
WHERE e.dept d.dept
AND e.employee_name _at_param1
AND d.dept_name _at_param2
/ Here the parameters are already converted
into UPPER and stored /
END

CREATE PROCEDURE proc1
_at_param1 UDD_EMPLOYEE_NAME,
_at_param2 UDD_DEPT_NAME
AS
BEGIN
SELECT _at_param1 UPPER(_at_param1)
SELECT _at_param2 UPPER(_at_param2)
SELECT emp_no, emp_name, dept_name
FROM employee e, department d
WHERE e.dept d.dept
AND UPPER(e.employee_name) _at_param1
AND UPPER(d.dept_name) _at_param2
/ Do not use UPPER against table column /
/ INSERT / UPDATE statement that saved data
should have converted the values to UPPER and
saved /
END

Use Is Null and Is Not Null operators to check
the null conditions.
e.g.,
Incorrect
SELECT ecode, ename
FROM emp
where name null
Correct
SELECT ecode, ename
FROM emp
where name Is Null

Do not use SET operator for assigning local
variables, use SELECT instead.
Not Recommended
SET _at_transaction_no_tmp 1
Recommended
SELECT _at_transaction_no_tmp 1

String literals in single quotes should be in
upper case.
All database object names, variable names should
be in lower case letters.
All Keywords, reserve words should be in upper
case letters
All DML statements should be in upper case
SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY,
GROUP BY, UPPER, LTRIM, RTRIM

Use parenthesis for complex arithmetic and
logical expressions or statements
Hash () temp tables should not be used. Creation
of tables increases the disk I/O. Instead We
can use Views, Table variable, Derived tables and
advanced SQL.
SELECT INTO statement should not be used inside
the procedure. This is a DDL statement. DBA would
set the database permission not to allow SELECT
INTO
Instead use a permanent temporary table and
Insert the records into the temporary table

For temporary tables, there should be a column
called GUID. GUID column should be a NOT NULL
column in the table.
When permanent temporary tables are used, then
delete should be done for the respective GUID
before and after the processing.
In all stored procedures referred by report,
dcube, view screens should have NOLOCK.

All SELECT statement should have NOLOCK table
hint. (Wherever dirty reads does not effect the
transaction).
e.g.,
Not Recommended
SELECT cust_name, cust_add, cust_phone_no
FROM customer a, address b
WHERE a.cust_id b.cust_id
Recommended
SELECT cust_name, cust_add,
cust_phone_no
FROM customer a (nolock), address b
(nolock)
WHERE a.cust_id b.cust_id
Avoid joining more than 5 tables in a single
select statement.

Database name, server name, user name should not
be hard coded in the procedures.
Do not return values from stored procedures like
functions.
Use output variables instead to return values to
the caller.
Output variables to be checked in all the calling
procedures
RAISERROR should be followed by a return
statement

Do not compare fields of different data types
Avoid while loops if the same can be achieved
using table joins.
Use SET NOCOUNT ON at the beginning of SQL
batches, stored procedures and triggers
This suppresses messages like '(1 row(s)
affected)' after executing INSERT, UPDATE, DELETE
and SELECT statements.
This improves the performance of stored
procedures by reducing network traffic.

Try Avoiding functions in where clause / Select
Statements.
e.g.,
Not Recommended
SELECT ename, eage, esal
FROM employee
WHERE LTRIM(ecode) LTRIM(_at_param1)
Recommended
SELECT _at_param1 LTRIM(_at_param1)
SELECT ename, eage, esal
FROM employee
WHERE ecode _at_param1

E.g. comparing a date field in table with
GETDATE() can be done after assigning the
GETDATE() value to a temporary variable and using
it in the query
Not Recommended
SELECT ename, eage, esal
FROM employee
WHERE joining_date lt getdate()
Recommended
SELECT _at_date_tmp getdate()
SELECT ename, eage, esal
FROM employee
WHERE joining_date lt _at_date_tmp

This is to be avoided as the function is executed
for each and every row selected / executed in the
WHERE / SELECT.
Ensure that all the major keys will be part of
the WHERE clause.
WHERE clause should contain the column names in
order of the index created on the table.
e.g., Index of Employee table is empid,
deptid
Not Recommended
SELECT empid,ename,esal
FROM employee
WHERE depid D01
AND empid E0010
Recommended
SELECT empid,ename,esal
FROM employee
WHERE empid E0010
AND depid D01

Where clause of a SELECT statement should include
the index column from major key onwards
e.g., Index on Employee_family table is empid,
Srl_no, family_member_name
Not Recommended
Index wont be used by query optimizer
SELECT empid,ename,
family_member_name
FROM Employee_family
WHERE srl_no 1
AND family_member_name XXXX
Recommended
SELECT empid,ename ,
family_member_name
FROM Employee_family
WHERE empid E0010
AND srl_no 1
AND family_member_name XXXX

Negative logic (Eg !, ltgt, not in) always
results in a table/index scan unless the query is
covered by the index covering.
Not Recommended
SELECT empname, deptid
FROM employee
WHERE deptid NOT IN (HR, ADMIN)
Recommended
SELECT empname, deptid
FROM employee
WHERE deptid IN (EDP, ACCTS)
Replace inequality operator in the search
argument with alternate operators.

EX
(1) SELECT
FROM orders
WHERE price ! 0
instead use
SELECT
FROM orders
WHERE price gt 0
(2) SELECT FROM orders
WHERE price 2 lt 500
instead use
SELECT
FROM orders
WHERE price lt 250

Use joins wherever possible, instead of using
sub-queries.
Not Recommended
SELECT cust_name, cust_total_purchase
FROM customer
WHERE cust_id in ( SELECT cust_id FROM
customer_sales )
Recommended
SELECT cust_name, cust_total_purchase
FROM customer a, customer_sales b
WHERE a.cust_id b.cust_id

When ALIAS name is given to the table/ view, then
use the ALIAS consistently in the query.
Do not mix aliases and table name in the query.
And do not use the table name as alias in the
query.
LIKE operation should NOT be done with INTEGER
data type in the WHERE clause.
Avoid the usage of HAVING clause wherever
possible as it uses a worktable.

DATE data type should not be compared with
INTEGER data type, i.e., before converting.
Always pass the date format parameter to the
CONVERT function while converting a string into
DATETIME.
E.g., _at_var1 CONVERT(datetime,_at_char_date1,120)
Always TRIM the variable, if a DATE is converted
into a CHARACTER data type. Store the converted
value in to a temporary variable instead of
inline comparison
E.g., _at_var1 ltrim(rtrim(CONVERT(varchar(10),_at_dat
e1,120)))

STAR () should not be given in result set.
E.g.
SELECT FROM table1
Here column names should be specified
explicitly.
For E.g.
SELECT from Customer can be written as
SELECT CustomerID, CustomerFirstName, City
FROM Customer
This technique results in reduced disk I/O and
better performance.

Do not return different result sets from the same
stored procedure.
In UNION/ UNION ALL statements ensure that the
corresponding selected columns match in DATATYPE.
Use UNION ALL when selecting Record sets which
are mutually Exclusive instead of UNION.
As UNION internally creates a worktable to sort
and select distinct rows. This will impact
performance.

table1 (col1 int, col2 int, col3 char(25), col4
char(4))
table2 (col11 int, col21 int, col31 char(25),
col41 int)
Wrong Union query col4 and col41 are not same
data type
SELECT col1, col2, col3, col4
FROM table1
UNION
SELECT col11, col21, col31, col41
FROM table1

ALIAS name should be given to the right of the
column.
E.g. SELECT col1 function, col2 position
Cursors should not be used.
Use Temporary tables, instead of cursors.
Order by, Distinct clause to be used minimally.
e.g., Index on Employee_family table is empid,
Srl_no, family_member_name
Not Recommended
Sort is unnecessary as the index covers the
sort keys. Sort would create a temporary table
and physically sort the rows which is a
performance overhead on query
SELECT empid,ename ,
family_member_name
FROM Employee_family
WHERE empid E0010
AND srl_no 1
AND family_member_name xxxx
ORDER BY empid, Srl_no, family_member_name

DISTINCT clause should be sparingly used
Many times DISTINCT is used because of bad query
which is returning multiple result rows
Wrong query
SELECT DISTINCT emp_name, dept_name, total_salary
FROM employee, employee_salary, dept
WHERE employee.dept dept.dept
AND employee.dept HR
AND employee.empid between 1000 and 2000
Above query would come out with multiple rows for
same employee
Problem is cartition product because employee
table and employee_salary table are not joined
on empid

Avoid Using IF NOT EXISTS.
Not Recommended
IF NOT EXISTS ( SELECT 1
FROM
customer
)
BEGIN
.. -- Logic Incorporated
END
Recommended
IF EXISTS ( SELECT 1
FROM
customer
)
BEGIN
select _at_tmp _at_tmp -- Do not
incorporate logic here unless it requires
END
ELSE
BEGIN
-- Logic Incorporated
END

Use _at__at_rowcount to minimize the SELECT on the
tables and IF EXISTS checks.
For Ex Consider the sample BR given below.
Given Part Pricelist should exists in the table
else throw error
Status of the part pricelist has to be "fresh"
else throw error

The Optimal code would be as follows
SELECT _at_Ppl_status ppl_status
FROM lttable_namegt
WHERE ppl_no _at_ppl_no
ltOther Conditionsgt
IF _at__at_ROWCOUNT 0
BEGIN
--Throw error for BR 1.
RAISERROR("Given Part Pricelist does not exists
in the table")
RETURN
END
IF _at_Ppl_status ltgt 'FRESH'
BEGIN
--Throw error for BR 2.
RAISERROR("Part Pricelist is not in Fresh
status, can not modify")

Do Not add dummy select statements in the Stored
procedure. For debugging take a copy of the
original procedure, rename the procedure and
debug.
For Header fetch and header refresh procedures,
there should be NO JOINS in the final select
statement, all values have to be fetched in
variables and these variables have to be used in
the Final select statement.

For Ex
For selecting the Part description at the header
level, don't make a join with the
Prt_PrCrl_CentralRefList_Info table in the final
select.
Get the Part no from the main table into a
variable _at_part_no.
From the table Prt_PrCrl_CentralRefList_Info,
select the part descriprion into a variable
_at_part_desc for the value in _at_part_no.Use
_at_part_desc in the final select statement.

Whenever insert statement is used, the column
names should be given explicitly.
Not Recommended
INSERT INTO customer
VALUES (C001,Jene, 24)
Recommended
INSERT INTO customer
(cust_id,cust_name,cust_age)
VALUES (C001,Jene, 24)
Incase a column is added in future
following this method will not raise an error

For Updates / Deletes on Main Table
( Page ) the Following checks should be
done
Existence Check
Status Check
Timestamp Check
These checks should also be done for Updates /
Inserts from Link Pages Also.

Document number generation after completing all
the checks and just before Insertion into the
main table.
Sequencing of Checks On Input Parameters
NULL Checks in the order of appearance on Screen
Existence Check if applicable in the Order of
Appearance on screen
Each business rule must be executed in the
sequence in which it is specified by the
functional consultant

All the DML Operations should be done only after
the validation checks been done. This will reduce
the unwanted I/O operations.
Avoid repeated reads on the same table, wherever
possible

Line comments inside select statement should not
be used
Do not write
Select _at_createdate getdate() -- System date
is stored as Creation Date
Instead write it as
/System date is stored as Creation Date/
Select _at_createdate getdate()

Try to avoid wildcard characters at the beginning
of a word while searching using the LIKE keyword,
as that results in an index scan, which defeats
the purpose of an index. The following statement
results in an index scan, while the second
statement results in an index seek
Not Recommended
SELECT LocationID FROM Locations WHERE
Specialities LIKE 'pples
Recommended
SELECT LocationID FROM Locations WHERE
Specialities LIKE 'As'

Use Derived tables wherever possible
e.g.,
SELECT MIN(Salary) FROM Employees WHERE
EmpID IN(SELECT TOP 2 EmpID FROM
Employees ORDER BY Salary Desc)
Can be written as
SELECT MIN(Salary) FROM (SELECT TOP 2
Salary FROM Employees ORDER BY Salary DESC) AS
A
The second query performs twice as fast as the
first query.

Do not call functions repeatedly in the procedure
instead assign it once to a variable and use it
across.
e.g.,
To find the Length of a string, to find
the current date use
SELECT _at_len_tmp LEN(name_tmp)
SELECT _at_date_tmp getdate()

ANSI-Standard Join clauses instead of the old
style joins. With ANSI joins, the WHERE clause is
used only for filtering data. Where as with older
style joins, the WHERE clause handles both the
join condition and filtering data.
e.g.,
Old Join
SELECT a.au_id, t.title FROM titles t, authors
a, titleauthor taWHERE a.au_id ta.au_id
ANDta.title_id t.title_id AND t.title LIKE
'Computer'

ANSI Join
SELECT a.au_id, t.titleFROM authors a INNER
JOINtitleauthor ta ON a.au_id ta.au_idINNER
JOINtitles tONta.title_id t.title_idWHERE
t.title LIKE 'Computer'

Usage of Alias for table names is mandatory
especially while joining tables.
Not Recommended
SELECT first_name, last_name,
country
FROM employee, countries
WHERE country_id id
AND lastname 'HALL'
Recommended
SELECT e.first_name, e.last_name,
c.country
FROM employee e, countries c
WHERE e.country_id c.id
AND e.last_name 'HALL'

Tables should be joined in the order of the
volume of data present in each table.
e.g., largest table should be joined
first, then the table with less data on so on.
The WHERE clause should be framed in such a
way that maximum no.of rows are filtered by the
first one or two conditions.
e.g.,
SELECT
FROM c, b, a, d
WHERE d.name 'JONES'
AND d.join_column 12345
AND d.join_column a.join_column
AND a.join_column b.join_column
AND b.join_column c.join_column

Do not use column numbers in the ORDER BY clause.
Consider the following example in which the
second query is more readable than the first
oneSELECT OrderID, OrderDateFROM OrdersORDER
BY 2SELECT OrderID, OrderDateFROM OrdersORDER
BY OrderDate