PL/SQL Best Practices

PL/SQL Best Practices

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

SQL Best Practices

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

Identifiers

Case 

• Use all Pascal cases for table and view names
• Use Pascal case for column names
• Use camel case for variables
• Use Pascal case for the stored procedure name
• Column names with white spaces between two words should be written within the brackets[]. E.g., [Student Name]
• Avoid using keywords for columns or table names.

Prefixes and suffixes

Use the following standard prefixes for database objects,

Object type	Prefix	Example
Primary key Clustered	pkc_	pkc_MyTable__Column
Primary key Non-clustered	pkn_	pkn_TB_TABLE__ColumnList
Index Clustered	ixc_	ixc_TS2_TABLE__Column
Index Non-clustered	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	tbl_, *_	tbl_TableName
Index	idx_	idx_IndexName
Function	fn_	fn_FuntionName
View	vw_	vw_QuestionResult
Trigger	tr_	tr_TriggerName
Sequence	seq_	seq_SequenceName
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_Calendar.sql
Conversion script	conv_	conv_Schedule.sql
Rollback script	rbk_	rbk_Schedule.sql

Save all scripts using the .sql extension. Use the full table name if a column references an Id in another table.

For example, use TitleId in table TB_AUTHOR to reference column Id or TitleId in table TB_TITLE.

Use all lowercase 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 OBJECT_ID('{owner}.{procedure name}', 'IsPRocedure') IS NOT NULL
BEGIN
DROP PROCEDURE {owner}.{procedure name};
END
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};
-- Add more variables as needed
SET {session variables}
-- Add more session variables as needed
{initialize variables};
{body of procedure};
RETURN;
EXCEPTION
WHEN {error type} THEN
{error handler};
END;
GO

Using the above SQL syntax, create a stored procedure in a SQL database. USE a statement to switch to the specified database and then check if the stored procedure already exists; if it does, drop it. Then CREATE PROCEDURE statement is used to create a new stored procedure, which is given a name, an owner, and a list of parameters; their data types are also provided.

Then DECLARE statement creates local variables, and the SET statement sets variables as a session. In the next step, it initializes variables, executes defined SQL statements, and finishes by including a RETURN statement and an EXCEPTION block that is used to catch and handle any errors that may occur while the procedure is running.

Formatting

Use single-quote characters to delimit strings. Nest single quotes to express a single quote or apostrophe within a string,

set @Example = '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 and 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. An overuse of multi-line comments may indicate a design that is not elegant. Choose identifier names that are self-documenting whenever possible.

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 unnecessary, use syntax that does not return a result set.

IF EXISTS (SELECT 1 
           FROM dbo.TB_Location 
           WHERE Type = 50)  
BEGIN  
IF ((SELECT COUNT(Id) FROM dbo.TB_Location WHERE Type = 50) > 0) 
END

• 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 a table that does not exist).

Always supply a friendly alias to the client when returning a variable or computed expression.

SELECT @identity AS ExamId,   
(@pointsReceived/ @pTotalPoints)AS Average 

Opt for a 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 its line. Each unrelated constraint within the where clause should appear on its line. 

SELECT 
    t.TaskId
FROM Task.dbo.TASK t
INNER JOIN 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 INTO SUBSCRIBE
EXECUTE SUBSCRIBERS_BUILDNEW_SYSTEM;

Use the following outline to insert statements moving values or variables into a single row. Place each column name and value on its line and indent both to match as shown.

Example,

INSERT INTO [dbo].[TB_Decision]
(
    [Id],
    [TestId],
    [qid],
    [DecisionId],
    [Comments]
)
VALUES
(
    1,
    1234,
    253535,
    1,
    'hello'
);
GO

Provide an inline comment to explain any hard-coded 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,

DELETE FROM [dbo].[TB_Decision]
WHERE Id = 1;

Transactions

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

BEGIN TRANSACTION [{transaction name}]

{statements}

IF {error}
BEGIN
    ROLLBACK TRANSACTION [{transaction name}];
END;
ELSE
BEGIN
    COMMIT TRANSACTION [{transaction name}];
END;

Transact-SQL Flow-of-control statements

Use the following outlines for if statements,

IF ({condition})
BEGIN
    {statement}
END;
ELSE IF ({condition})
BEGIN
    {statement}
END;
ELSE
BEGIN
    {statement}
END;

Use the following outlines for a while statements,

WHILE ({condition})
BEGIN
    {statement}
END;

Use the following outlines for case statements. (Note that the SQL case construct is not a

SELECT 
    CASE [{column or variable}]
        WHEN {value | expression} THEN {result if 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. (Rarely)
• 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 need 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 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 them and then finally create them.
• While adding columns, foreign keys, and primary keys to the table, always check for their existence first.
• Add a 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,

Below SQL statement is to create the table. If the table exists, then drop and create a new table.

IF OBJECT_ID('dbo.Types') IS NOT NULL
BEGIN
    DROP TABLE Types;
END;
GO

CREATE TABLE [dbo].[Types](
    [uid] INT IDENTITY(1, 1) NOT NULL,
    [DocumentDescription] NVARCHAR(200) NOT NULL,
    [QuestionID] VARCHAR(20) NOT NULL
);
GO

Below the SQL statement is Alter table script to add a new column,

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Questions'
    AND COLUMN_NAME = 'IsMandatory'
)
BEGIN
    ALTER TABLE [dbo].[Questions]
    ADD IsMandatory BIT NULL;
END;
GO

Below the SQL statement is Alter table script to drop a column,

IF EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Elements'
    AND COLUMN_NAME = 'Abbreviation'
)
BEGIN
    ALTER TABLE [dbo].[Elements]
    DROP COLUMN [Abbreviation];
END;
GO

Below the SQL statement are Alter table scripts to add primary keys and foreign keys,

IF OBJECT_ID('PK_Info') IS NULL
BEGIN
    ALTER TABLE [dbo].[Info]
    ADD CONSTRAINT [PK_Info]
    PRIMARY KEY CLUSTERED ([uid] ASC);
END;
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.foreign_keys
    WHERE name = 'FK_Payments_Type'
)
BEGIN
    ALTER TABLE [dbo].[Payments]
    ADD CONSTRAINT [FK_Payments_Type]
    FOREIGN KEY ([TypeID]) REFERENCES [dbo].[Type]([TypeID])
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
END;
GO

Below the SQL statement is Alter table script to add the Default constraint,

ALTER TABLE [dbo].[QA]
ADD CONSTRAINT [DF_QA_Type_2]
DEFAULT 'C' FOR [Type];
GO

Below the SQL statement is Alter table script to Drop a constraint,

IF EXISTS (
    SELECT * FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[DF__QA__Type__2]')
    AND type = 'DA'
)
BEGIN
    ALTER TABLE [dbo].[QA]
    DROP CONSTRAINT [DF__QA__Type__2];
END;
GO

Below SQL statement is Creating/ Altering Stored Procedures,

IF EXISTS (
    SELECT * FROM sys.objects
    WHERE type = 'P'
    AND name = 'AddRequest'
)
BEGIN
    DROP PROCEDURE [dbo].[AddRequest];
END;
GO

CREATE PROCEDURE [dbo].[AddRequest]
AS
BEGIN  
   -- add the body of the procedure here
END;
GO

Below the SQL statement is the data update script to Insert records,

IF NOT EXISTS (
    SELECT * FROM [TYPE]
    WHERE TYPEID = 'RESUBMIT'
)
BEGIN
    INSERT INTO [TYPE](TYPEID)
    VALUES('RESUBMIT');
END;

Below the SQL statement is the data update script to Delete records,

IF EXISTS (
    SELECT * FROM TYPE
    WHERE TYPEID = 'RESUBMIT'
)
BEGIN
    DELETE FROM TYPE
    WHERE TYPEID='RESUBMIT'
END;