A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

The CTE is preferred to use as an alternative to a Subquery/View. A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.

CTE Vs temp table :
CTE has its uses - when data in the CTE is small and there is strong readability improvement as with the case in recursive tables. However, its performance is certainly no better than table variables and when one is dealing with very large tables, temporary tables significantly outperform CTE.

     WITH Employee_CTE (EmployeeNumber, Title)
     AS
    (SELECT NationalIDNumber,JobTitle FROM   HumanResources.Employee)
    
     SELECT EmployeeNumber,Title FROM   Employee_CTE

    // CTE To Get Nth Highest Salary using ROW_NUMBER()

    with ordered_salary as
    (
        SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rn FROM salary_table
    )
    select name, salary from ordered_salary where rn = 5

                                                    

Here is the solution for nth highest salary from employees table using DENSE_RANK()

    SELECT FIRST_NAME , SALARY FROM 
    (
      SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK FROM EMPLOYEES
    )
    WHERE SALARY_RANK = n; 
                                                    

Here is the solution for nth highest salary from employees table using CTE and ROW_NUMBER()

    with ordered_salary as
    (
    SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rn FROM salary_table
    )
    select name, salary from ordered_salary where rn = 5 
                                                    

Here is the solution for nth highest salary from employees table dept wise using CTE and DENSE_RANK() and partition by

    With CTE AS  
    (  
        select DENSE_RANK() over (partition by depid order by salary desc) As DR,  
        DepId,Name,Salary from EmpDeptWise  
    )  
    
    Select DepId,Name,Salary from CTE where DR=N
                                                    

As a developer, we know any SQL query can be written in multiple ways but we should follow best practices/ techniques to achieve better query performance.

• Use EXISTS instead of IN to check existence of data.
• Avoid * in SELECT statement. Give the name of columns which you require.
• Choose appropriate Data Type. E.g. To store strings use varchar in place of text data type. Use text data type, whenever you need to store large data (more than 8000 characters).
• Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
• Avoid NULL in fixed-length field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.
• Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
• Create Clustered and Non-Clustered Indexes.
• Keep clustered index small since the fields used in clustered index may also used in non-clustered index.
• Most selective columns should be placed leftmost in the key of a non-clustered index.
• Drop unused Indexes.
• Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
• Use joins instead of sub-queries.
• Use WHERE expressions to limit the size of result tables that are created with joins.
• Use TABLOCKX while inserting into a table and TABLOCK while merging.
• Use WITH (NOLOCK) while querying the data from any table.
• Use SET NOCOUNT ON and use TRY- CATCH to avoid deadlock condition.
• Avoid Cursors since cursor are very slow in performance.
• Use Table variable in place of Temp table. Use of Temp tables required interaction with TempDb database which is a time taking task.
• Use UNION ALL in place of UNION if possible.
• Use Schema name before SQL objects name.
• Use Stored Procedure for frequently used data and more complex queries.
• Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
• Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
• Avoid use of Non-correlated Scalar Sub Query. Use this query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch.
• Avoid Multi-statement Table Valued Functions (TVFs). Multi-statement TVFs are more costly than inline TVFs.

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here

Sample answer here