PL/SQL Best Practices

This article lists some important SQL Best Practices with examples.

In an enterprise data-driven applications, the quality of SQL statements used in stored procedures play a vital role in applications' performance. Bad SQL statements can break the application. In this article, I discuss some of the best practices of using PL/SQL.
 

SQL Best Practices

 
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.
 

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,
  1. use{database name}  
  2. if(objectProperty(object_id('{owner}.{procedure name}'),'IsPRocedure')is notnull)  
  3.      drop procedure{owner}.{procedure name}  
  4. GO  
  5. createprocedure {owner}.{procedure name}  
  6.       [{parameter}  {data type}][,…]  
  7. as  
  8. /*******************************************************************  
  9. PROCEDURE: {procedure name}  
  10. * PURPOSE: {brief procedure description}  
  11. * NOTES: {special set up or requirements, etc.}  
  12. * CREATED:  {developer name} {date}  
  13. * MODIFIED   
  14. DATE            AUTHOR                  DESCRIPTION  
  15. *-------------------------------------------------------------------  
  16. * {date}          {developer} {brief modification description}  
  17. *******************************************************************/  
  18. [declare {variable name} {data type}[,…]]  
  19. [{set session}]  
  20. [{initialize variables}]  
  21. {bodyof procedure}  
  22. return  
  23. {error handler}  
Formatting
 
Use single-quote characters to delimit strings. Nest single-quotes to express a single-quote or apostrophe within a string,
  1. set @sExample = 'Bills example'  
Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions,
  1. 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.
    1. Ifexists(select 1   
    2. from dbo.TB_Location   
    3. whereType = 50)  
    4. rather than,  
    5. if ((selectcount(Id)  
    6. from dbo.TB_Location   
    7. 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.
  1. select@@identity as ExamId,   
  2. (@pointsReceived/ @pTotalPoints)as Average 
Opt for more descriptive alias.
  1. select@@identity as UserId   
  2. is preferred over   
  3. 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. 
  1. select t. TaskId  
  2. from Task.dbo.TASK t   
  3. innerjoin Task.dbo.ENROLLMENT et   
  4. on t.TaskId= et. TaskId  
  5. where et.MemberId = @pMemberId  
  6. and((t.Due_DT<= @pStartDate)  
  7. or(t.DueDaTe>= @pEndDate)  
  8. 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,
  1. 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,
  1. insert into [dbo].[TB_Decision]  
  2. ([Id]  
  3. ,[TestId]  
  4. ,[qid]  
  5. ,[DecisionId]  
  6. ,[Comments])  
  7. values  
  8. (1  
  9. ,1234  
  10. ,253535  
  11. ,1  
  12. ,'hello')  
  13. 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,
  1. update [dbo].[TB_Decision]  
  2. set  
  3. [TestId] = 12343  
  4. ,[qid]= 111  
  5. ,[DecisionId]= 111  
  6. ,[Comments]= 111  
  7. where [Id] = 1 
Deletes

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

Example,
  1. deletefrom [dbo].[TB_Decision]  
  2. 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,
  1. BeginTransaction [{transaction name}]  
  2. {statements}  
  3. If{error}  
  4. CommitTransaction [{transaction name}]  
  5. else  
  6. RollbackTransaction [{transaction name}]  
Transact-SQL Flow-of-control statements
 
Use the following outlines for if statements,
  1. if({condition})  
  2. {statement}  
  3. else  
  4. if({condition})  
  5. {statement}  
  6. else  
  7. {statement}   
  8. or  
  9. if({condition})  
  10. begin  
  11. {statement}  
  12. .  
  13. .   
  14. .  
  15. {statement}  
  16. end  
  17. else  
  18. {statement}  
Use the following outlines for while statements,
  1. while ({condition})  
  2. {statement}   
  3. or   
  4. while ({condition})  
  5. begin  
  6. {statement}  
  7. .  
  8. .   
  9. .  
  10. {statement}  
  11. end  
Use the following outlines for case statements. (Note that the SQL case construct is not a
  1. selectcase [{column or variable}]  
  2. when{value | expression} then {resultif this value}  
  3. [when {value | expression} then {result if this value}]  
  4. [else {default result}]]  
  5. 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,
  1. IF OBJECT_ID('dbo.Types')IS NOTNULL  
  2. DROPTABLE Types;  
  3. GO  
  4. CREATE TABLE [dbo].[Types](  
  5.    [uid] INTIDENTITY (1, 1) NOTNULL,  
  6.    [DocumentDescription] NVARCHAR (200)NOT NULL,  
  7.    [QuestionID] VARCHAR (20)NOT NULL  
  8. );  
  9. GO  
Alter table script to add new column,
  1. IFNOT EXISTS  
  2. (  
  3.    SELECT 1  
  4.    FROMINFORMATION_SCHEMA.COLUMNS  
  5.    WHERE TABLE_SCHEMA = 'dbo'  
  6.    AND TABLE_NAME = 'Questions'  
  7.    AND COLUMN_NAME = 'IsMandatory'  
  8. )  
  9. ALTERTABLE [dbo].[Questions]  
  10. ADD IsMandatory BIT NULL;  
  11. GO 
Alter table script to drop a column,
  1. IF EXISTS  
  2. (  
  3.    SELECT 1  
  4.    FROMINFORMATION_SCHEMA.COLUMNS  
  5.    WHERE TABLE_SCHEMA = 'dbo'  
  6.    AND TABLE_NAME = 'Elements'  
  7.    AND COLUMN_NAME = 'Abbreviation'  
  8. )  
  9. ALTERTABLE [dbo].[Elements]DROP COLUMN [Abbreviation];  
  10. GO  
Alter table scripts to add primary keys and foreign keys,
  1. IFOBJECT_ID('PK_Info')IS NULL  
  2. ALTERTABLE [dbo].[Info]ADD CONSTRAINT [PK_Info]   
  3. PRIMARYKEY CLUSTERED([uid] ASC);  
  4. GO  
  5. IF NOTEXISTS  
  6. (  
  7.    SELECT 1  
  8.    FROMsys.foreign_keys  
  9.    WHERE name = 'FK_Payments_Type'  
  10. )  
  11. ALTERTABLE [dbo].[Payments]  
  12. ADDCONSTRAINT [FK_Payments_Type]FOREIGN KEY ([TypeID])REFERENCES [dbo].[Type]([TypeID])ON DELETENO ACTIONON UPDATE  
  13. NO  
  14. ACTION;  
  15. GO 
Alter table script to add Default constraint,
  1. ALTERTABLE [dbo].[QA]ADD CONSTRAINT [DF_QA_Type_2] DEFAULT ('C')FOR [Type]  
  2. GO 
Alter table script to Drop a constraint,
  1. IFEXISTS (SELECTFROM dbo.sysobjectsWHERE id =OBJECT_ID(N'[DF__QA__Type__2]')AND type= 'DA')  
  2. BEGIN  
  3. ALTER TABLE [dbo].[QA]DROP CONSTRAINT [DF__QA__Type__2]  
  4. END  
  5. GO 
Creating/ Altering Stored Procedures,
  1. IF EXISTS (SELECT* FROMsys.objectsWHERE type= 'P'AND name ='AddRequest')  
  2. DROPPROCEDURE [dbo].[AddRequest]  
  3. GO  
  4. /****** Object: StoredProcedure [dbo].[AddRequest] Script Date: 12/12/2011 09:03:53 ******/  
  5. SETANSI_NULLS ON  
  6. GO  
  7. SET QUOTED_IDENTIFIER ON  
  8. GO   
  9. CREATE PROCEDURE [dbo].[xxxx]   
  10. GO  
Data update script to Insert records,
  1. IFNOT EXISTS(SELECT* FROMTYPE WHERE TYPEID = 'RESUBMIT')  
  2. BEGIN  
  3. INSERTINTO TYPE(TYPEID)VALUES('RESUBMIT')  
  4. END  
Data update script to Delete records,
  1. IFEXISTS (SELECT* FROMTYPE WHERE TYPEID = 'RESUBMIT')  
  2. BEGIN  
  3. DELETEFROM TYPEWHERE TYPEID='RESUBMIT'  
  4. END