Reader Level:
TUTORIAL

PL/SQL Best Practices

Posted by Subal Mishra Tutorials | SQL June 15, 2012
In an enterprise data-driven application, the quality of SQL statements plays a vital role in an application's performance. A bad SQL statement can break the application. In this article, I discuss some best practices of using PL/SQL.
  • 1
  • 0
  • 6418

The key aspect and purpose of coding standards has always been to make development & maintenance easier for developers. Best Practices make the job even easier. In order to satisfy the requirement for making maintenance easier for the developers, coding standards must address several areas of the development process.

SQL Best Practices

Identifiers

  • Case
     
    • Use all Pascal case for table and view names
    • Use Pascal case for column names
    • Use camel case for variables
    • Use Pascal case for stored procedure name
    • Column names with white space between two words should be written within the brackets[]. E.g [Student Name]
    • Avoid using keyword for columns or tables name.
    •  
  • Prefixes and suffixes
    • Use the following standard prefixes for database objects:
       
      Object type Prefix Example
      Primary key Clustered

      pkc_

      pkc_MyTable__Column
      Primary key Nonclustered

      pkn_

      pkn_TB_TABLE__ColumnList
      Index Clustered

      ixc_

      ixc_TS2_TABLE__Column
      Index Nonclustered

      ixn_

      ixn_TB_TABLE__ColumnList
      Foreign key

      fk_

      fk_THIS_TABLE__ColumnB__to__TB_PKEY_TABLE__ColumnA
      Unique Constraint

      unq_

      unq_TB_TABLE__Column_List
      Check Constraint

      chk_

      chk_TB_TABLE__Column
      Column Default

      dft_

      dft_TB_TABLE_ColumnList
      Passed Parameter

      @p

      @pPassedVariableName
      Local Variable

      @

      @VariableName
      Table

      TB_, *_

      TB_TableName
      View

      VW_

      VW_QuestionResult
      User Defined Scalar Function

      ufs_

      ufs_GetOccBucketValue
      User Defined Table Function

      uft_

      uft_GetOcc
      Stored Procedure

      usp_

      usp_GetId,usp_InsertCase,

      usp_UpdateCase,usp_InsertUpdate,

      usp_ AnalystTestAllocationRpt

      Note :Not use sp_ as it is for system stored procedures.
       
    • Use the following standard prefixes for scripts:
       
      Script type Prefix Example
      Stored procedure script

      proc_

      proc_Calendar.sql
      Schema script

      def_

      def_Calender.sql
      Conversion script

      conv_

      conv_Schedule.sql
      Rollback script

      rbk_

      rbk_Schedule.sql
       
    • Save all scripts using the .sql extension
    • If a column references an Id in another table, use the full table name. For example, use TitleId in table TB_AUTHOR to reference column Id or TitleId in table TB_TITLE.
    •  
  • Use all lower case for system names, statements, variables, and functions:

    • Reserved words (begin, end, table, create, index, go, identity).
    • Built-in types (char, int, varchar).
    • System functions and stored procedures (cast, select, convert).
    • System and custom extended stored procedures (xp_cmdshell).
    • System and local variables (@@error, @@identity, @value).
    • References to system table names (syscolumns).
       
  • Stored Procedures (and other dml scripts)

    • Use the following outline for creating stored procedures:

      use{database name}
      if(objectProperty(object_id('{owner}.{procedure name}'),'IsPRocedure')is notnull)
           drop procedure{owner}.{procedure name}
      GO
      create
      procedure {owner}.{procedure name}
            [{parameter}  {data type}][,…]
      as
      /*******************************************************************
      * PROCEDURE: {procedure name}
      * PURPOSE: {brief procedure description}
      * NOTES: {special set up or requirements, etc.}
      * CREATED:  {developer name} {date}
      * MODIFIED
      * DATE            AUTHOR                  DESCRIPTION
      *-------------------------------------------------------------------
      * {date}          {developer} {brief modification description}
      *******************************************************************/
      [declare {variable name} {data type}[,…]]
      [{set session}]
      [{initialize variables}]
      {bodyof procedure}
      return
      {error handler}
    •  
  • Formatting
     
    • Use single-quote characters to delimit strings. Nest single-quotes to express a single-quote or apostrophe within a string:

         
      set @sExample = 'Bills example'
       
    • Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions:

         
      if((select 1 where 1 = 2) isnot null)
       
    •  Use begin..end blocks only when multiple statements are present within a conditional code segment.
       
  • Whitespace

    • Use one blank line to separate code sections.
    • Do not use white space in identifiers
    •  
  • Comments

    • Use single-line comment markers where needed (--). Reserve multi-line comments (/*..*/) for blocking out sections of code.
    • Comment only where the comment adds value. Don't over-comment, and try to limit comments to a single line. Choose identifier names that are self-documenting whenever possible. An overuse of multi-line comments may indicate a design that is not elegant.
       
  • DML Statements (select, insert, update, delete)

    • A correlated subquery using "exists" or "not exists" is preferred over the equivalent "in" or "not in" subquery due to performance degradation potential in some cases using "not in".
    • Avoid the use of cross-joins if possible.
    • When a result set is not needed, use syntax that does not return a result set.

      Ifexists(select 1
      from dbo.TB_Location
      whereType = 50)
      rather than,
      if ((selectcount(Id)
      from dbo.TB_Location
      whereType = 50) > 0)
       
    • If more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred.
    • Always use column names in an "order by" clause. Avoid positional references.
       
  • Select

    • Do not use a select statement to create a new table (by supplying an into table that does not exist).
    • When returning a variable or computed expression, always supply a friendly alias to the client.

      select
      @@identity as ExamId,
      (@pointsReceived/ @pTotalPoints)as Average
       
    • Opt for more descriptive alias.

      select
      @@identity as UserId
      is preferred over
      select@@identity as Id
       
    • Use the following outline for select statements. Each column in the select list should appear on it's own line. Each unrelated constraint within the where clause should appear on it's own line.

      select t. TaskId
      from Task.dbo.TASK t
      innerjoin Task.dbo.ENROLLMENT et
      on t.TaskId= et. TaskId
      where et.MemberId = @pMemberId
      and((t.Due_DT<= @pStartDate)
      or(t.DueDaTe>= @pEndDate)
      or(et.FLAG= 1))
       
  • Inserts

    • Always list column names within an insert statement. Never perform inserts based on column position alone.
    • Do not call a stored procedure during an insert as in:

      insert SUBSCRIBE execute SUBSCRIBERS_BUILDNEW_SYSTEM
       

    • Use the following outline for insert statements moving values or variables into a single row. Place each column name and value on it's own line and indent both so they match as shown.

      Example:

      insertinto [dbo].[TB_Decision]
      ([Id]
      ,[TestId]
      ,[qid]
      ,[DecisionId]
      ,[Comments])
      values
      (1
      ,1234
      ,253535
      ,1
      ,'hello')
      GO
       
    • Provide an inline comment to explain any hardcoded value.

    •  
  • Updates

    Use the following outline for simple update statements. Format the where clause as described earlier.

    Example:

    update [dbo].[TB_Decision]
    set
    [TestId] = 12343
    ,[qid]= 111
    ,[DecisionId]= 111
    ,[Comments]= 111
    where [Id] = 1
     
  • Deletes

    Use the following outline for simple delete statements. Format the where clause as described earlier.

    Example:

    deletefrom [dbo].[TB_Decision]
    where Id =1
     
  • Transactions

    If a transaction is necessary for a multi-statement operation, and the code will not be managed by an OLEDB client connection, use:

    BeginTransaction [{transaction name}]
    {statements}
    If{error}
    CommitTransaction [{transaction name}]
    else
    RollbackTransaction [{transaction name}]
     
  • Transact-SQL Flow-of-control statements

    • Use the following outlines for if statements:
       
      if({condition})
      {statement}
      else
      if({condition})
      {statement}
      else
      {statement
      or
      if({condition})
      begin
      {statement}
      .
      .

      .
      {
      statement}
      end
      else
      {statement}
       
    • Use the following outlines for while statements:

      while ({condition})
      {statement
      or 
      while ({condition})
      begin
      {statement}
      .
      .

      .
      {
      statement}
      end
       
    • Use the following outlines for case statements. (Note that the SQL case construct is not a

      selectcase [{column or variable}]
      when{value | expression} then {resultif this value}
      [when {value | expression} then {result if this value}]
      [else {default result}]]
      end
       
  • Cursors

    • Use cursors only where a set based operation is inappropriate. (Almost never)
    • Never use a cursor to return data to the application. The performance hit for this is unacceptable.
       
  • Before Executing Scripts, please make sure of the following:

    • Make sure all the scripts are Re-Runnable.
    • All data update scripts needs to be checked carefully for not using any identity columns directly as they can be different in different versions of the databases.
    • dbo.XXXX or XXXX (where XXXX is the object name) should be used explicitly in the script to avoid creating any objects with the person's userid.
    • Only use Alter statements when adding columns, removing columns, changing foreign key constraints, changing default columns etc.
    • Never use Alter statements for Views, functions and Stored Procedures.
    • When creating any database object, check for its existence first. If objects exist, drop it and then finally create it.
    • While adding columns, foreign keys, primary keys to the table, always check for its existence first.
    • Add USE statement at the beginning of every script.
    • Filenames should not create any spaces. Use underscores to separate names if necessary.
    • Every script should end with GO.
       
  • Some examples of Re-runnable scripts

    • Create table script:

      IF
      OBJECT_ID('dbo.Types')IS NOTNULL
      DROPTABLE Types;
      GO
      CREATE
      TABLE [dbo].[Types](
      [uid] INTIDENTITY (1, 1) NOTNULL,
      [DocumentDescription] NVARCHAR (200)NOT NULL,
      [QuestionID] VARCHAR (20)NOT NULL
      );
      GO
       
    • Alter table script to add new column:

      IFNOT EXISTS
      (
      SELECT 1
      FROMINFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'Questions'
      AND COLUMN_NAME = 'IsMandatory'
      )
      ALTERTABLE [dbo].[Questions]
      ADD IsMandatory BIT NULL;
      GO
       
    • Alter table script to drop a column:

      IFEXISTS
      (
      SELECT 1
      FROMINFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'Elements'
      AND COLUMN_NAME = 'Abbreviation'
      )
      ALTERTABLE [dbo].[Elements]DROP COLUMN [Abbreviation];
      GO
       
    • Alter table scripts to add primary keys and foreign keys:

      IFOBJECT_ID('PK_Info')IS NULL
      ALTERTABLE [dbo].[Info]ADD CONSTRAINT [PK_Info]
      PRIMARYKEY CLUSTERED([uid] ASC);
      GO
      IF
      NOTEXISTS
      (
      SELECT 1
      FROMsys.foreign_keys
      WHERE name = 'FK_Payments_Type'
      )
      ALTERTABLE [dbo].[Payments]
      ADDCONSTRAINT [FK_Payments_Type]FOREIGN KEY ([TypeID])REFERENCES [dbo].[Type]([TypeID])ON DELETENO ACTIONON UPDATE
      NO
      ACTION
      ;
      GO
       
    • Alter table script to add Default constraint:

      ALTERTABLE [dbo].[QA]ADD CONSTRAINT [DF_QA_Type_2] DEFAULT ('C')FOR [Type]
      GO
       
    • Alter table script to Drop a constraint:

      IFEXISTS (SELECT* FROM dbo.sysobjectsWHERE id =OBJECT_ID(N'[DF__QA__Type__2]')AND type= 'DA')
      BEGIN
      ALTER
      TABLE [dbo].[QA]DROP CONSTRAINT [DF__QA__Type__2]
      END
      GO
       
    • Creating/ Altering Stored Procedures:

      IF
      EXISTS (SELECT* FROMsys.objectsWHERE type= 'P'AND name ='AddRequest')
      DROPPROCEDURE [dbo].[AddRequest]
      GO
      /****** Object: StoredProcedure [dbo].[AddRequest] Script Date: 12/12/2011 09:03:53 ******/
      SETANSI_NULLS ON
      GO
      SET
      QUOTED_IDENTIFIER ON
      GO 
      CREATE
      PROCEDURE [dbo].[xxxx]
      GO
       
    • Data update script to Insert records:

      IFNOT EXISTS(SELECT* FROMTYPE WHERE TYPEID = 'RESUBMIT')
      BEGIN
      INSERTINTO TYPE(TYPEID)VALUES('RESUBMIT')
      END
       
    • Data update script to Delete records:

      IFEXISTS (SELECT* FROMTYPE WHERE TYPEID = 'RESUBMIT')
      BEGIN
      DELETEFROM TYPEWHERE TYPEID='RESUBMIT'
      END

COMMENT USING

Trending up