PL/SQL Best Practices

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.

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
    createprocedure {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