Facebook
From BM, 9 Months ago, written in Plain Text.
This paste is a reply to SQL Notes from Biswaranjan - view diff
Embed
Download Paste or View Raw
Hits: 455
  1. The SQL Server Joins are classified into two types such as
  2.  
  3. ANSI format JOINS
  4. NON-ANSI format JOINS
  5.  
  6. ANSI -
  7. Inner join
  8. Outer join
  9. Cross join
  10.  Further, the outer join is divided into three types are as follows
  11.     Left outer join
  12.     Right outer join
  13.     Full outer join
  14.    
  15. NON - ANSI
  16. EQUI join
  17. NON-EQUI join
  18. SELF-join
  19. Natural Join
  20.  
  21. Note :- Default JOIN in SQL is Inner join
  22.  
  23. INDEX:-
  24.  
  25. Note - In a table, we can use a maximum of 1000 indexes (1 Clustered Index plus 999 Non-Clustered Index).
  26.  
  27. When an index is created, a balanced tree which has root, non-leaf and leaf nodes are created. Hence the search becomes faster.
  28.  
  29. Clustered index is created by default only when primary key is present for the table.
  30. 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.
  31.  
  32. What is a Covering query?
  33. 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.
  34.  
  35. Aspect                  | RANK() Function         | DENSE_RANK() Function   |
  36. -------------------------|--------------------------|--------------------------|
  37. Rank Assignment         | Assigns unique ranks     | Assigns unique ranks     |
  38. Handling Tied Rows      | Skips ranks for tied rows | No gaps in rank sequence |
  39. Tied Rows              | Receive different ranks if tied | Receive the same rank if tied |
  40. Rank Values             | Non-consecutive (may have gaps) | Consecutive (no gaps)   |
  41. Example SQL            | RANK() OVER (ORDER BY Column) | DENSE_RANK() OVER (ORDER BY Column) |
  42. Use Case                | When gaps in ranks are acceptable | When consecutive ranks are preferred for tied rows |
  43.  
  44. Window Functions:-
  45.  
  46. Aggregate Functions :- Avg, min, max, count, sum
  47. Ranking funtions :- rank, dense_rank, row_number, ntile
  48. value functions :- lag, lead, first_value, last_value, nth_value
  49.  
  50. SELECT
  51.     Date,
  52.     Price,
  53.     Price - LAG(Price, 1) OVER (ORDER BY Date) AS DailyChange,
  54.     LAST_VALUE(Price) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EndOfWeekPrice
  55. FROM
  56.     StockPrices;
  57.    
  58. Stored procedure:-
  59. When we execute a sql query it goes through 3 process:Syntax Checked,Plan Selected,Query Execution.
  60.  
  61. 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
  62.  
  63. 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.
  64.  
  65. Temporary/private Stored procedure
  66. CREATE PROCEDURE #GlobalProcedue
  67. AS
  68. BEGIN
  69.   PRINT 'This is Temporary Temporary Procedure'
  70. END
  71.  
  72. GLobal SP:-
  73. CREATE PROCEDURE ##GlobalProcedue
  74. AS
  75. BEGIN
  76.   PRINT 'This is Global Temporary Procedure'
  77. END
  78.  
  79. FUNCTIONS :-
  80.  
  81. User defined Scalar function:- Meaning it returns only one value
  82.   CREATE FUNCTION AddTwoNumber(@firstNo INT, @second INT)
  83.   RETURNS INT
  84.   AS
  85.   BEGIN
  86.   DECLARE @result INT
  87.       SET @result = @firstNo + @second
  88.       RETURN @result
  89.   END
  90.   We can call using
  91.   Select dbo.AddTwoNumber
  92.      or
  93.   DECLARE @result INT
  94.   SET @result = (Select dbo.AddTwoNumber(10,15))
  95.   PRINT @result
  96.  
  97. User defined Inline Table Valued function
  98.  It returns a whole table and its return type is TABLE
  99.       CREATE FUNCTION FN_GetStudentDetailsByID
  100.       (
  101.         @ID INT
  102.       )
  103.       RETURNS TABLE
  104.       AS
  105.       RETURN (SELECT * FROM Student WHERE ID = @ID)
  106.      
  107.        We can call it using just like a table But with ()
  108.        SELECT * FROM FN_GetStudentDetailsByBranch(‘CSE’)
  109.        
  110. User defined Multi-statement Table Valued Function
  111.  
  112. -- Multi-statement Table Valued function: In this we create our own return table definition
  113. CREATE FUNCTION MSTVF_GetEmployees()
  114. RETURNS @Table Table (ID int, Name nvarchar(20), DOB Date)
  115. AS
  116. BEGIN
  117.   INSERT INTO @Table
  118.     SELECT ID, Name, Cast(DOB AS Date)
  119.     FROM Employee
  120.   Return
  121. End
  122.  
  123. Calling:-
  124. SELECT * FROM MSTVF_GetEmployees()
  125.        
  126. Functions VS Procedures
  127.  
  128. https://dotnettutorials.net/lesson/multi-statement-table-valued-function-in-sql-server/
  129.  
  130. Deterministic function
  131. 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.
  132.  
  133. Non deterministic function
  134. 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
  135.  

Replies to Re: SQL Notes rss

Title Name Language When
Re: Re: SQL Notes BM1 text 9 Months ago.