1 / 50

SQL Query Writing Guidelines

SQL Query Writing Guidelines. 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

enrico
Download Presentation

SQL Query Writing Guidelines

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

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

  4. 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: <Name of the person> */ • /* Modified Date: < Date of modification > */ • /* Bug No: <No of the bug for which the modification is done> */ • /* Bug Description: <Short description of the bug> */

  5. 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 UDD’s for declaring local variables • Procedure/ Function parameter’s UDD should match with that of the method parameters.

  6. 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 @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

  7. CREATE PROCEDURE proc1 @param1 UDD_EMPLOYEE_NAME, @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(@param1) AND d.dept_name = UPPER(@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

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

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

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

  11. Do not use SET operator for assigning local variables, use SELECT instead. Not Recommended SET @transaction_no_tmp = 1 Recommended SELECT @transaction_no_tmp = 1

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

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

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

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

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

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

  18. Try Avoiding functions in where clause / Select Statements. e.g., NotRecommended SELECT ename, eage, esal FROM employee WHERE LTRIM(ecode) = LTRIM(@param1) Recommended SELECT @param1 = LTRIM(@param1) SELECT ename, eage, esal FROM employee WHERE ecode = @param1

  19. 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 NotRecommended SELECT ename, eage, esal FROM employee WHERE joining_date < getdate() Recommended SELECT @date_tmp = getdate() SELECT ename, eage, esal FROM employee WHERE joining_date < @date_tmp

  20. 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 NotRecommended 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’

  21. 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 NotRecommended – 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’

  22. Negative logic (Eg : !=, <>, 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.

  23. EX : (1) SELECT * FROM orders WHERE price != 0 instead use SELECT * FROM orders WHERE price > 0  (2) SELECT * FROM orders WHERE price * 2 < 500   instead use  SELECT * FROM orders WHERE price < 250

  24. Use joins wherever possible, instead of using sub-queries. NotRecommended 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

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

  26. 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., @var1 = CONVERT(datetime,@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., @var1 = ltrim(rtrim(CONVERT(varchar(10),@date1,120)))

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

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

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

  30. 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 NotRecommended – 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

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

  32. Avoid Using IF NOT EXISTS. NotRecommended 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

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

  34. The Optimal code would be as follows : SELECT @Ppl_status = ppl_status FROM <table_name> WHERE ppl_no = @ppl_no <Other Conditions> 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

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

  36. 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 @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.

  37. Whenever insert statement is used, the column names should be given explicitly. NotRecommended 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

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

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

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

  41. 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()

  42. 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: NotRecommended SELECT LocationID FROM Locations WHERE Specialities LIKE '%pples‘ Recommended SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'

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

  44. 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()

  45. 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%'

  46. 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%'

  47. 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'

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

  49. 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, OrderDateFROM OrdersORDER BY 2SELECT OrderID, OrderDateFROM OrdersORDER BY OrderDate

  50. Handling Null Columns in the table • Make it a practice to include ISNULL check for every NULL column in a table. • All NOT NULL columns in a table would have NOT NULL values • ‘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

More Related