Writing And Optimizing SQL Query

Think before writing a SQL Query

Understand Business Requirements: understand the requirement properly first from the Stakeholder/ Product Owner/ Team.
Follow the 5 W’s: Who? What? Where? When? Why?
Follow the most optimized way
The sequence of keywords (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
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.

SQL

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

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

SQL

DROP TABLE IF EXISTS #tmpTable
DROP PROC IF EXISTS Proc_Name

CONCAT_WS

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

SQL

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

It will simply ignore Null values.

OUTPUT

SQL,Server,New,Feature,2017

TRIM

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.

APPROX_COUNT_DISTINCT

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.