Development Emergency: SQL Coding Guidelines

n When ALIAS name is given to the table/ view, then use the ALIAS consistently in the query.
n Do not mix aliases and table name in the query. And do not use the table name as alias in the query.
n LIKE operation should NOT be done with INTEGER data type in the WHERE clause.
n Avoid the usage of HAVING clause wherever possible as it uses a worktable.
n DATE data type should not be compared with INTEGER data type, i.e., before converting.
n Always pass the date format parameter to the CONVERT function while converting a string into DATETIME.
n E.g., @var1 = CONVERT(datetime,@char_date1,120)
n 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
n E.g., @var1 = ltrim(rtrim(CONVERT(varchar(10),@date1,120)))
n STAR (*) should not be given in result set.
E.g.
SELECT * FROM table1
Here column names should be specified explicitly.
n For E.g. :
  SELECT * from Customer can be written as

SELECT CustomerID, CustomerFirstName, City                   FROM Customer
n This technique results in reduced disk I/O and better performance.
n Do not return different result sets from the same stored procedure.
n In UNION/ UNION ALL statements ensure that the corresponding selected columns match in DATATYPE.
n Use UNION ALL when selecting Record sets which are mutually Exclusive instead of UNION.
n As UNION internally creates a worktable to sort and select distinct rows. This will impact performance.
n table1 (col1 int, col2 int, col3 char(25), col4 char(4))
n table2 (col11 int, col21 int, col31 char(25), col41 int)
n Wrong Union query – col4 and col41 are not same data type
n SELECT col1, col2, col3, col4
n FROM table1
n UNION
n SELECT col11, col21, col31, col41
n FROM table1
n ALIAS name should be given to the right of the column.
E.g. SELECT col1 function, col2 position
n Cursors should not be used.
n Use Temporary tables, instead of cursors.
n 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 =   ‘Sudha’
ORDER BY empid, Srl_no, family_member_name

n DISTINCT clause should be sparingly used
n 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

n 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 @tmp = @tmp   -- Do not incorporate logic here                                            unless it requires
END
ELSE
BEGIN
            ……   -- Logic Incorporated
END

n Use @@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

n The Optimal code would be as follows :
SELECT @Ppl_status = ppl_status
FROM
WHERE ppl_no =             @ppl_no

IF @@ROWCOUNT = 0
BEGIN
--Throw error for BR 1.
RAISERROR("Given Part Pricelist does not exists in the table")
RETURN
END

IF @Ppl_status <> 'FRESH'
BEGIN
--Throw error for BR 2.
RAISERROR("Part Pricelist is not in Fresh status, can not modify")
RETURN
END

n Do Not add dummy select statements in the Stored procedure. For debugging take a copy of the original procedure, rename the procedure and debug.

n 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.
n 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 ( like CWO header table) into a variable @part_no.
From the table Prt_PrCrl_CentralRefList_Info, select the part descriprion into a variable @part_desc for the value in @part_no.Use @part_desc in the final select statement.

n 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

n 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.

n Document number generation after completing all the checks and just before Insertion into the main table.
n 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

n All the DML Operations should be done only after the validation checks been done. This will reduce the unwanted I/O operations.
n If the logic happened to be same across procedures, write a common procedure/function and call it in the respective procedures.
n Avoid repeated reads on the same table, wherever possible
n Line comments inside select statement should not be used
Do not write
Select @createdate = getdate() -- System            date is stored as Creation Date
Instead write it as
/*System date is stored as Creation Date*/
Select @createdate = getdate()
n 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 'A%s'

n 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.

n 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 @len_tmp = LEN(name_tmp)
SELECT @date_tmp = getdate()

n 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 ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
t.title LIKE 'Computer%'

ANSI Join
SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE 'Computer%'

n 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'

n 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

n 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 one:

SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

n Handling Null Columns in the table
n Make it a practice to include ISNULL check for every NULL column in a table.
n All NOT NULL columns in a table would have NOT NULL values
n ‘Blank Space’ is not equal to NULL
e.g., Here MiddleName is stored as null in customer table
SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM Customers
The result is Null here.

Instead use IsNull or Coalesce function
SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
FROM Customers

Development Emergency

Thursday 12 June 2014

SQL Coding Guidelines - II