Writing And Optimizing SQL Query

Think before writing a SQL Query

  1. Understand Business Requirements: understand the requirement properly first from the Stakeholder/ Product Owner/ Team.
  2. Follow the 5 W’s: Who? What? Where? When? Why?
  3. Follow the most optimized way
  4. The sequence of keywords (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
  5. Always use capital letters for keywords

Things to keep in mind 

  • SELECT <fields> instead of using SELECT <*>
  • Avoid Subqueries – as it searches row by row, which impacts the SQL query
  • Avoid SELECT Distinct – we can select more fields to create unique results.
  • Use INNER Join instead of join with Where - To make this understand, let’s take an example, suppose there are 100 employees with 100 team members, then the query will first generate 10,000, then filter from 100 records where Employee ID is correctly joined, which results in inefficient use database resource.
  • Use WHERE instead of HAVING to filter the data.
  • Use indexing
  • Avoid loops, it slows the execution time.
  • Use EXITS () for matching if the record exists.
  • Use Wildcards to search 
  • Use NOLOCKS() with SELECT
  • Stored procedure name should be small and it should not start with SP_ keyword
  • If we need only table result then use SET NO COUNT ON
  • Avoid dynamic query
  • Avoid Cursor – as it takes up memory and creates locks
  • Use table variable instead of temp table when working on small data.
  • If using system defined functions multiple times then put that in a variable and use that variable. Example – DECLARE @Date=GETDATE () and use @Date wherever required.
  • Avoid multiple spaces.
  • Always use appropriate data types.
  • Use query execution plan and SQL profiler to check the cost.

New Features

Condition Drop Statement

Whenever we create any temp table, we drop it after use, it's a best practice.

Example to drop a temp table.

IF OBJECT_ID(N'tempdb..#tmpTable) IS NOT NULL
DROP TABLE #tmpTable

But, now we have a new way to do it by using Conditional drop statement, which will be applicable for Table, Procedure, Function, etc.,



CONCAT_WS can concatenate strings that might have "blank" or Null values - for example,

SELECT CONCAT_WS(',','SQL', NULL, NULL, 'Server', 'New', 'Feature', 2017) AS 'Result';

It will simply ignore Null values.




Earlier we were using LTRIM() and RTRIM() to remove white spaces from both sides of string. Now in SQL Server 2017 we have a new keyword TRIM() which handle white space cleanup from both side.


Until now we used Count(distinct column name) to get the count of record. Now in SQL Server 2019 we have a new function APPROX_COUNT_DISTINCT(). It uses less memory and CPU resources.