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
