Best Practices in SQL Code - SQL Code Guide

Best Practices in SQL Code - SQL Code Guide

• 1.
  November 20, 2025TCS Public Best Practices
• 2.
  2 November 20, 2025 10Simple ways for effective PL/SQL Coding
• 3.
  3 November 20, 2025 Datatype Declaration Data type Declaration • Use the correct data type. • %Type and %ROWTYPE attributes while defining data type. • Always prefix Global and Local variables uniformly. Advantages: Advantages: • There will be a reduction in the time taken for implicit conversion. • Avoidance of the necessary code change when schema structure changes. • There will be clear understanding of the nature and use of the variable.
• 4.
  4 November 20, 2025 FramingSQL Queries Framing SQL Queries • The number of sql query’s used should be minimal. example: select tab1.col1, tab2.col2 from tab1,tab2 where tab1.col1=<condition> and tab2.col1=<condition> The above query is better than writing the same in a cursor or in two different sqls’. Advantages Advantages • This will reduce the number of database accesses. • Performance of the code will be improved.
• 5.
  5 November 20, 2025 Selectionof driving table Selection of driving table • Always use the tables with least number of records as the driving table. • ORACLE parser processes table names from right to left. (the table specified last is the driving table). • First the driving table is scanned and sorted. • The other table is then scanned and merged all the rows retrieved with the first table. Example: Table TAB1 has 1000 rows, TAB2 has 10 rows. efficient method: Select count(*) from TAB1,TAB2. Advantages Advantages • Performance of the query will be faster and efficient.
• 6.
  6 November 20, 2025 AvoidUsing ‘*’ in SELECT clause Avoid Using ‘*’ in SELECT clause •All the fields that are to be selected or inserted must be specified in the query rather than using ‘*’ symbol. •Always use table aliases and prefix column names by their aliases when more than one table is involved. Advantages Advantages •The valid column names need not be obtained from data dictionary and substituted at command line which is time consuming. •There will be a reduction in parsing time if table aliases are used.
• 7.
  7 November 20, 2025 UseEXISTS instead of IN Clause Use EXISTS instead of IN Clause • Always use EXISTS in place of IN Clause. example SELECT tab1.column_name FROM tab1 WHERE tab1.colum_name EXISTS (SELECT tab2.column_name FROM tab2).
• 8.
  8 November 20, 2025 UseNOT EXISTS instead of NOT IN Clause Use NOT EXISTS instead of NOT IN Clause • Always use NOT EXISTS in place of NOT IN Clause. • The NOT IN clause is found to be the slowest as it forces a full read of the table in the sub-query SELECT. example SELECT tab1.column_name FROM tab1 WHERE tab1.colum_name NOT EXISTS (SELECT tab2.column_name FROM tab2).
• 9.
  9 November 20, 2025 Positionof Joins in WHERE Clause Position of Joins in WHERE Clause • Table joins should be written before any condition of the WHERE Clause. • The condition which filter out the maximum records should be placed at the end after the joins. • Generally join tables rather than using EXISTS or IN Clause. example (Least efficient way) SELECT tab1.col2 FROM tab1 WHERE EXISTS (SELECT 1 FROM tab2 WERE tab2.col1=tab1.col1 AND tab2.col2= <condition>) (Efficient way) SELECT tab1.col2 FROM tab1,tab2 WHERE tab1.col1=tab2.col1 AND tab2.col2=<condition>
• 10.
  10 November 20, 2025 DynamicSQL Dynamic SQL •Dynamic sql is an effective way of framing the query at run time depending on the functionality. Use Dynamic SQL when one of the following items is unknown •Text of the SQL statement (commands, clauses, and so on) •The number of host variables •References to database objects such as columns, indexes, sequences, tables, usernames, and views Advantages Advantages •The code change required whenever the functionality changes will be minimum and less time consuming.
• 11.
  11 November 20, 2025 Requirementsfor a Dynamic SQL Requirements for a Dynamic SQL • A character string must contain the text of a valid SQL statement. • The character string can contain dummy host variables. • The dummy host variables hold places in the SQL statement for actual host variables • The text should not contain ';' statement terminator • The text should not contain the following SQL commands DECLARE DESCRIBE EXECUTE FETCH OPEN WHENEVER example 'INSERT INTO TAB1(COL1, COL2) VALUES (:value1, :value2)'
• 12.
  12 November 20, 2025 Useof views for User Interface Use of views for User Interface • If the architecture is a centralized database server, use views which retrieves region specific data and displays in the user interface. Advantages Advantages • The users will access to only their specific region data. • The same table can be used to store all region data. • The same table can be accessed by multiple users.
• 13.
  13 November 20, 2025 Triggers Triggers •Try to minimize the use of Triggers. • Use BEFORE Trigger only for modifying the new value. • Use BEFORE Trigger for auditing. • Use AFTER Trigger for validations.
• 14.
  14 November 20, 2025 Whythis is a Best Practice Why this is a Best Practice Advantages Advantages • Easy to understand and edit. • Reduction in maintenance cost. • Improvement in Performance of the code. Contact Info Contact Info Sriram Vanchi Nathan Sriram Vanchi Nathan sriram.vanchinathan@tcs.com sriram.vanchinathan@tcs.com
• 15.
  15 November 20, 2025