The SQL Server Joins are classified into two types such as

ANSI format JOINS
NON-ANSI format JOINS

ANSI - 
Inner join
Outer join
Cross join
 Further, the outer join is divided into three types are as follows
    Left outer join
    Right outer join
    Full outer join
    
NON - ANSI
EQUI join
NON-EQUI join
SELF-join
Natural Join

Note :- Default JOIN in SQL is Inner join

INDEX:-

Note - In a table, we can use a maximum of 1000 indexes (1 Clustered Index plus 999 Non-Clustered Index).

When an index is created, a balanced tree which has root, non-leaf and leaf nodes are created. Hence the search becomes faster.

Clustered index is created by default only when primary key is present for the table.
In Non clustered,the leaf node actually holds the data and in the case of a non-clustered index, the leaf node actually points to the leaf node of a clustered index or points to the heap table if the table does not have any clustered index.

What is a Covering query?
If all the columns that we have requested in the select clause of the query are present in the index, then there is no need to look up the table again. The requested column data can simply be returned from the index. This is called Covering Query.

Aspect                  | RANK() Function         | DENSE_RANK() Function   |
-------------------------|--------------------------|--------------------------|
Rank Assignment         | Assigns unique ranks     | Assigns unique ranks     |
Handling Tied Rows      | Skips ranks for tied rows | No gaps in rank sequence |
Tied Rows              | Receive different ranks if tied | Receive the same rank if tied |
Rank Values             | Non-consecutive (may have gaps) | Consecutive (no gaps)   |
Example SQL            | RANK() OVER (ORDER BY Column) | DENSE_RANK() OVER (ORDER BY Column) |
Use Case                | When gaps in ranks are acceptable | When consecutive ranks are preferred for tied rows |

Window Functions:-

Aggregate Functions :- Avg, min, max, count, sum
Ranking funtions :- rank, dense_rank, row_number, ntile
value functions :- lag, lead, first_value, last_value, nth_value

SELECT
    Date,
    Price,
    Price - LAG(Price, 1) OVER (ORDER BY Date) AS DailyChange,
    LAST_VALUE(Price) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EndOfWeekPrice
FROM
    StockPrices;
    
Stored procedure:-
When we execute a sql query it goes through 3 process:Syntax Checked,Plan Selected,Query Execution.

When we create a stored procedure, the syntaxes are checked while creating the procedure or we can say at the design. When we execute the procedure for the first time, the best execution plan is selected and is cached in memory. And after that whenever we call the stored procedure, the query execution plan is taken from the cache rather than creating again and again and executed

NOTE:-So, in SQL Server by using the return values, we can return only one integer. It is not possible, to return more than one value using return values, whereas in output parameters, we can return any data type and a stored procedure can have more than one output parameter.

Temporary/private Stored procedure
CREATE PROCEDURE #GlobalProcedue
AS
BEGIN
  PRINT 'This is Temporary Temporary Procedure'
END

GLobal SP:-
CREATE PROCEDURE ##GlobalProcedue
AS
BEGIN
  PRINT 'This is Global Temporary Procedure'
END

FUNCTIONS :-

User defined Scalar function:- Meaning it returns only one value
  CREATE FUNCTION AddTwoNumber(@firstNo INT, @second INT)
  RETURNS INT
  AS
  BEGIN
  DECLARE @result INT
      SET @result = @firstNo + @second
      RETURN @result
  END
  We can call using
  Select dbo.AddTwoNumber 
     or
  DECLARE @result INT
  SET @result = (Select dbo.AddTwoNumber(10,15))
  PRINT @result
  
User defined Inline Table Valued function
 It returns a whole table and its return type is TABLE
      CREATE FUNCTION FN_GetStudentDetailsByID
      (
        @ID INT
      )
      RETURNS TABLE
      AS
      RETURN (SELECT * FROM Student WHERE ID = @ID)
      
       We can call it using just like a table But with ()
       SELECT * FROM FN_GetStudentDetailsByBranch(‘CSE’)
       
User defined Multi-statement Table Valued Function

-- Multi-statement Table Valued function: In this we create our own return table definition
CREATE FUNCTION MSTVF_GetEmployees()
RETURNS @Table Table (ID int, Name nvarchar(20), DOB Date)
AS
BEGIN
  INSERT INTO @Table
    SELECT ID, Name, Cast(DOB AS Date)
    FROM Employee
  Return
End

Calling:-
SELECT * FROM MSTVF_GetEmployees()
       
Functions VS Procedures

https://dotnettutorials.net/lesson/multi-statement-table-valued-function-in-sql-server/

Deterministic function
The Deterministic functions in SQL Server always return the same result every time they are called with a specific set of input values and give the same state of the database. For examples: SQUARE(), Power(), Sum(), AVG(), and Count(). All aggregate functions are deterministic functions. Let us see some examples.

Non deterministic function
The Non-deterministic functions in SQL Server may return different results each time they are called with a specific set of values even if the database state that they access remains the same. For examples: GetDate() and Current_Timestamp