Writing And Optimizing SQL Query

How to write effective sql queries and how to optimize it. Few new features of sql that should be handy for every Sql developer.

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
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.,

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,

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.