Use SET NOCOUNT ON

This statement is used to stop the message, which shows the number of rows affected by SQL statement like INSERT, UPDATE and DELETE.
Ex.
SET NOCOUNT ON
It will remove this extra overhead from the network.
SET NOCOUNT ON

Use schema name before objects.

It helps SQL Server to find the object.
Ex. SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee
If you want to change the schema, you can change.
  1. IF(NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'emp'))
  2. BEGIN
  3. EXEC('CREATE SCHEMA [emp] AUTHORIZATION [dbo]')
  4. END
  5. ALTER SCHEMA emp
  6. TRANSFER dbo.Employees
It will improve the performance of the stored procedure

Use EXISTS () instead of COUNT ()

This SQL optimization technique concerns the use of EXISTS (). If you want to check if a record exists, use EXISTS () instead of COUNT (). While COUNT () scans the entire table. This will give you better performance.
Ex. SELECT Count(1) FROM dbo.Employee
Ex. IF ( EXISTS (SELECT 1 FROM db.Employees))
BEGIN
END

Don’t use functions in the WHERE clause

While writing select query, if you use the function in where condition, it will reduce the performance of your query. Try to avoid the function in where clause.

Use NO LOCK

Use NOLOCK will improve the performance of the select query
  1. SELECT EmpID,EmpName,EmpSalary
  2. FROM dbo.Employee WITH(NOLOCK)
  3. WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

Specify column names instead of using * in SELECT statement

Try to avoid *
  1. SELECT * FROM dbo.Employee WITH(NOLOCK)
  2. WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)
Best practice is to specify the column name.
  1. SELECT EmpID,EmpName,EmpSalary
  2. FROM dbo.Employee WITH(NOLOCK)
  3. WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

Avoid temp temporary table

Temporary tables usually increase a query’s complexity. It’s suggested to avoid the temporary tables.

Create Proper Index

Proper indexing will improve the speed of the operations in the database.

Use Join query instead of sub-query and co-related subquery

Using JOIN is better for the performance than using subqueries or nested queries
Also, use minimum JOINS (as required) and check whether proper JOIN is used or not.

Read More >>