SQL Concepts - amitbhilagude/userfullinks GitHub Wiki

  1. Overview

    1. Cast, Convert, Formatting date and DATENAMe Converting Data type. Additional parameter length.
    2. JOIN ON
    3. CONCAT, CONCAT_WS, TRIM
    4. CASE WHEN THEN
    5. BETWEEN AND
    6. NOT BETWEEN AND
    7. IN, NOT IN
    8. TOP, TOP TIES, TOP PERCENT
    9. OFFSET, FETCH
    10. VARIABLE
    11. NESTED SELECT

  2. JOINS

    1. Join or Inner Join( Default)
      1. Return only matched records from where criteria
    2. LEFT JOIN
      1. All records for first table and matched records of second table
    3. RIGHT JOIN
    4. Full JOIN
      1. Combine record from both table from where criteria

  3. Merge Operators

    1. UNION
    2. UNION ALL
    3. INTERSECT
    4. EXCEPT

  4. Temporary Table

  5. SQL Best Practices

    1. Never use *
    2. Always use comments /../
    3. Use try catch
    4. Always add Alias e.g. employee table is e.columnname when there is join

  6. Dynamic Data Masking (SQL 2016 and above)

    1. Full : Entire column. Supported Any type.
    2. Partial: Show starting and ending of characters. Supported for String type.
    3. Email: Specially for Email. Supported for string.
    4. Random: Entire column is replaced with Random number. Supported for Number.
    5. Drop Temporary table

  7. Normalisation

    1. Set of rules used for database
    2. Rules
      1. 1NF(First Normal Form)
        1. Every table needs to have a primary key
        2. Column should not have multiple values
        3. the Same column should not be repeated
        4. Solution to avoid point 2 and 3 is create another table with unique kay and create a relationship between the first and second table
      2. 2NF(Second Normal Form)
        1. It is applicable for Compound primary key i.e. Primary key is a combination of two columns
        2. All non-primary columns should be dependent on the compound primary key only and it shouldn't be retrieved with a single key. That means Non-primary key values shouldn't be duplicated
        3. Solution to avoid this is duplicate columns should be maintained in the separate table along with mapping. 3, 3NF(Third Normal form)
        4. THis is an application for Primary kay and there are duplicated value into non-primary columns
        5. E.g. Address table which contains City, area and postal code. We can still get the Postal code from City and area so we can have a separate table for the same.

  8. Terminology

    1. Pages:
      1. Fundamental unit of data storage
      2. 1 MB size will have 128 pages
    2. Extends
      1. Collection of 8 continuous pages is called Extends
    3. Heap
      1. Heap is a collection of pages but not in any particular order and there is no linking of pages
    4. Clustered Index
      1. B-Tree data structure where leaf nodes are pages with the doubly linked list data structure
      2. B-Tree has a root node and intermediate nodes keep track of the range of pages.
      3. Leaf Nodes have actual data rows.
    5. Non-Clustered Index
      1. Same as clustered index however leaf nodes have keys and not data

  9. Indexing

    1. When a table gets created in SQL, It creates a clustered index for the Primary key automatically and you have the option to override this and create a non clustered index.
    2. Creating a lot of indexes or duplicating indexes may degrade the performance of queries and here it needs to be careful when to choose and which columns need indexing. One example Pluralsight mentioned how insert queries took time more after indexing.