Six Simple Steps to Improve Stored Procedures in SQL Server

1. SET NOCOUNT ON: Stop the message that shows the count of rows affected by a T-SQL statments. These messages can be suppressed with SET NOCOUNT ON and can increase performance by decreasing network traffic

2. IF EXISTS(): Use IF EXISTS() to check if data exist in a table or not. You can write something to check if one record exists in a temp table. If yes, then drop the table before re-creating it again. IF EXISTS() helps a lot you are using #<TempTableName> expressions to create temporary table in a stored procedure.

IF EXISTS(Select 1 from #<TempTableName>)
(
   DROP #TempTable
)

3. Avoid SELECT * FROM <TableName> : Load only required columns in a stored procedures. Using SELECT * will make stored procs slow and increase the overall cardinality.

/*Don't use this in your stored procs*/
SELECT * FROM Audience 

/*Instead write something like this: */

SELECT Id, Age, Gender FROM Audience

4. Transfer Data from Primary table using Table variables : Using table variables i.e Temp tables, CTE you can transfer required data from primary table into table variables.

/*CTE Example*/

WITH Audience_CTE (Id, Age)
AS
(SELECT Id,
        Age
 FROM   Audience)
SELECT Id,
       Age
FROM   Audience_CTE 

/*Insert Databases names into SQL Temp Table*/
 
SELECT Id, UserName, Age, Gender
INTO #TempTable 
FROM Audience
Where Id > 200 and Gender = 'Male'

5. Use Indexes (Clustered/Non-Clustered):
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. [Source]

/*  Creating a Temp table then adding Indexes on it*/
SELECT Id, UserName, Age, Gender
INTO #TempTable 
FROM Audience

CREATE CLUSTERED INDEX IDX_ID ON #TempTableA(Id)

CREATE INDEX IDX_UserName ON #TempTableA(UserName)

6. Use sql_executesql instead Execute Dyanmic SQL Query :
The sp_executesql allows for cache plan reuse and protects from SQL Injection.

/* Avoid Below Query*/
SET @Query ='SELECT * FROM Audience where Id>'@AudienceID
EXECUTE (@Query)

/*Insted write in this way*/
SET @Query ='SELECT * FROM Audience where Id >@AudienceID'
EXECUTE sp_executesql @Query,N'@AudienceID INT,@AudienceID=253