INDEXPage-SqlTutorial-1: Microsoft SQL Server Training: Free Tutorials and Online-Learning Classes (OLCs)


Microsoft SQL Server Training:
Free On-line Tutorials
And
On-line Learning Classes(OLCs)

Tutorial-1:
Database Development Using T-SQL



Sql-Tutorial1-Microsoft-SQL-Server-Training-Free-Tutorials-and-Online-Learning-Classes

W elcome to T-SQL training course. Finally you got right place to learn T-Sql in easy, efficient, task-oriented and well-organized manner. The training process categorized into THREE(3)-Effective tutotrials and each one delievers a task- oriented valuable material organized into Online-Learning Classes (OLCs). Each OLC has its scope and vision. However, first tutorial classes, provides you with maximum stuff and satisfaction to learn and implement Sql Query. The Tutorial-2 taught you how you can do Db-Programming using T-SQL; this includes writing Stored Procedures, Functions (UDF), Triggers, Cursors and Transaction using T-SQL constructs; The final Tutorial-3, explains how can you do database modeling using E-R diagrams and following predefined RDBMS rules.

INDEX
  1. Lesson-1: Learning DB-Table Operations

  2. Lesson-2: Learning Integrity Constraints

  3. Lesson-3: Learning Basic DML / CRUD Operations

  4. Lesson-4: Learning Advanced - DML /CRUD operations

  5. Lesson-5: Learning Advance SELECT

  6. Lesson-6: Learning JOIN-operation (with All DML- CRUD operations)

  7. Lesson-7: Learning Sub-Queries

  8. Lesson-8: Learning Temp-Tables:

  9. Lesson-9: Learning INDEXes

  10. Lesson-10: Learning VIEWS

  11. Lesson-11: Learning Built-in functions

  12. Lesson-12: Learning Database Operations


        I hope you would like and enjoy reading all OLCs (Online Learning Classes) that I have try to explain the concept precisely. I will do my best to write and update the posts as well on time to ensure you people get all stuff at single place.

       The approach I follow here will help you to quickly locate the appropriate token, associated sections with bunch of examples to ensure your completeness. I thing, you will find this would be substitutable to reading books or class-room teaching approaches. I hope so, happy learning.

Audience

  • The targeted audience could be beginners to help them understand the basic of Transact-Sql.
  • The targeted audience could be intermediate-level & expertise professional to take reference while implementing database objects.

Feature tutorials:

  1. Tutorial-2: Database Programming using T-SQL
  2. Tutorial-3: Database Designing

SqlTutorial-1 : Lesson-5 : Class-1 : Part-1
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Advanced-SELECTs:
Common Table Expression (CTE) - WITH Statement



Microsoft SQL Server Training Online Learning Classes Advance SELECT Operations Common Table Expression CTE

CTE is named expression that holds result of Sub-select temporarily. The main advantage of CTE is light-weight and recursion; hence it is ultimate choice for navigating hierarchical data.
INDEX:
  1. Overview of CTE
    • Aspects of CTE
    • Advantages of CTE
    • Limitations of CTE
    • Main Usages:
  2. Working with CTE
    • Syntax
    • Examples:
  3. Recursive CTE- Basic understanding
  4. Recursive CTE-Hierarchical data processing
  5. Conclusion


1. Overview of CTE


1.1. Aspects of CTE

  1. A CTE is essentially a disposable view, and can be used in place of derived tables.
  2. CTE is defined by WITH-Keyword, and it is best practice to begin CTE with Semi-column, since CTE is not single-statement it is batch of statement.
  3. CTE can be used in SELECT, INSERT, UPDATE or DELETE statement. But SELECT is desired.
  4. CTE is single-time visible to sub-sequent query only.
  5. CTE can be used to define VIEWS as part of View’s SELECT query.
  6. CTE can be defined as Recursive-CTE or Non-Recursive-CTE;
  7. Recursive-CTE calls itself whereas Non-Recursive does not. It is best practice to use MAXRECURSION to prevent infinite loops in Recursive-CTE.
  8. CTE (Recursive) mainly used for navigating hierarchical data.
  9. CTE (Non-Recursive) is substitutable to derived-table (or Inline-view or Sub-Query); Since CTE and Derived Table are non-persistent and have same visibility.
  10. CTE is not substitutable to Temp-Table or Table-Variable; Because CTE and TempTable are differ based on: Visibility and Persistency; CTE visibility is limited to only a single SQL statement that would very first to it; whereas Temp-Table or Table-Variable is visible to at-least current scope where they defined and Temp-Table are persistent to Db.
  11. CTE can be defined in SUB-ROUTIENS such as user-defined functions, stored procedures triggers or views.
  12. You can define cursor on CTE-referenced Select Query.

1.2. Advantages of CTE

Main advantage of CTE is light-weight and Recursion. The other advantages are: Readability and maintenance;
  1. Light-weight: CTEs are light-weight than Temporary-tables (or Table-Variable). Since, they are non-persistent and do not write any schema information or business-data to disk. The second thing is that CTEs get cleaned up automatically by SQL Server. Therefore, CTE does not create any maintenance over-head and no performance impact.
  2. Recursion: A recursive-CTE is ultimate for navigating hierarchical data;
  3. Readability: CTE improves readability; this is awesome aspect that developers always look for. CTE separates data extraction from your main query. Consider a complex query containing multiple joins, data filter operations, group by etc. Using CTE, you can put all JOIN-operations in one CTE-variable; and all filters in another CTE variable then conveniently access them in your query. This will rapidly increase readability of your query. Hence, CTE is substitutable to Derived Table.

1.3 Limitations of CTE

  • CTE-Select cannot include following clauses:
    • DISTINCT
    • INTO
    • ORDER BY (except when a TOP clause is specified)
    • GROUP BY
    • OPTION clause with query hints
    • FOR BROWSE
  • CTE-cannot include PIVOTed or Un-PIVOTed data;

2.3. Main Usages by Examples:

  1. Extracting Hierarchical data (see Recursive-CTE)
  2. Self-joining Sub-query result-set (see last-example)
  3. Increasing readability for complex query

2. Working with Common-Table-Expression (CTE)

2.1. Syntax:

WITH expression_name [ ( column_name[,...n] ) ]
AS ( CTE_query_definition )
Where
  • expression_name = The name of the common table expression.
  • column_name [ ,...n ] = The unique column names of the expression.
  • CTE_query_definition = The SELECT query that defines the common table expression.

2.2. Examples:

  1. Simple-CTE: Defining a table expression with WITH statement.

    ;WITH MySearch (ID, f_Name)
             AS ( SELECT ID,first_Name FROM employee)
     Select * from MySearch 
  2. Simple-CTE2: Defining CTE without Column aliasing

    ;WITH MySearch
             AS ( SELECT ID,first_Name FROM employee)
     Select * from MySearch 
  3. Multiple CTEs using single WITH AS statement.

    ;WITH A AS (select * from Table1 ),
           B AS (select id, name Table2),
           C AS (Select name from Table3)
    Select * from A,B,C;
  4. CTE with SET-operators.

    WITH cte_Sample1
         AS (SELECT name from TABLE1 UNION ALL SELECT name from TABLE2)
    Select * from cte_Sample1;
  5. Implementing self join to derived table using CTE.

    WITH Temp AS (SELECT * FROM Table1)
    SELECT t1.*, t2.*
     FROM Temp t1 INNER JOIN Temp t2 ON t1.ID = t2.ID;


3. Recursive CTE - Basic understanding

  • Recursive-CTE allows you to write hierarchical queries.
  • Recursive-CTE referenced by itself;
  • Recursive-CTE executes in BFS(Breadth-First-Search) order. For DEF(Depth-First-Search) order make anchor(outer) query ORDER BY a path;
  • Recursive-CTE works as cursor in first iteration AnchorQuery executed and in sub-sequent iteration RecursiveQuery executed.

3.1. Syntax- Recursive-CTE

;WITH cte_name ( Column_name [,...n] )
AS (
       Select Query                       - - Anchor member is defined
       UNION ALL 
       Select Query referencing cte_name   - - Recursive member
)
Outer_Query [OPTION (MAXRECURSION N) ]
Where:
  1. Anchor Select Query is executed only once and it holds result of recursion. But Recursive Select query executed multiple times repeatedly.
  2. The FROM-Clause of Recursive-Select must refer CTE at most once.
  3. The Anchor-Select and Recursive-Select should combined together by UNION-ALL
  4. The no. of and type of columns in Anchor-Select and Recursive-Select must be same.
  5. Use MAXRECURSION to prevent infinite loop in Recursive-CTE 

3.2. Examples

  1. Simple-Recursive-CTE with single anchor query

    WITH MyCTE(x)
    as
    (
            Select x = 'hello'
            UNION ALL
            Select x + 'a' from MyCTE where len(x) < 10
    )
    select x from MyCTE
    order by x;

    OUTPUT :
    hello
    helloa
    helloaa
    helloaaa
    helloaaaa
    helloaaaaa
  2. Simple Recursive-CTE: Multiple Anchor Queries

    with MyCTE(x)
    as
    (
    select x = 'hello'
    union all
    select x = 'goodbye'
    union all
    select x + 'a' from MyCTE
    where len(x) < 10
    )
    select x from MyCTE
    order by len(x), x

    OUTPUT :
    hello
    helloa
    goodbye
    helloaa
    goodbyea
    helloaaa
    goodbyeaa
    helloaaaa
    goodbyeaaa
    helloaaaaa
  3. Multiple-Recrusive Queries in CTE

    WITH MyCTE(x)
    As
    (   Select x = 'hello'
        UNION ALL
        Select x + 'a' from MyCTE where len(x) < 10
        UNION All Select x + 'b' from MyCTE where len(x) < 10
     )
    Select x
    From MyCTE
    Order by len(x), x

    OUTPUT
    hello
    helloa
    hellob
    helloaa
    helloab
    helloba
    hellobb
    helloaaa
    helloaab
    helloaba
    helloabb
    hellobaa
    hellobab
    hellobba
    hellobbb
    helloaaaa
    ….
    63 rows of output.


4. Hirarchical Data Processing using Recursive-CTE

Let us consider a self-reference Table: Persons

4.1: Providing Hierarchical Context to Table

WITH cte_Person (Id, Name, Designation, Manager, [Level] , [Root] )
AS
(
      SELECT Id, Name, Designation, Manager, 0, Id
      FROM Person
      WHERE Manager IS NULL

      UNION ALL

      SELECT p.Id, p.Name, p.Designation, p.Manager, [Level] + 1, [Root]
      FROM Person p
      INNER JOIN cte_Person cte_p ON p.Manager = cte_p.Id
)
SELECT * FROM cte_Person;

Where [Level] and [Root] are derived columns not avialable in Table and evaluated by Recrusive-CTE;

Pictorial representation of above Hierarchical data


4.2: Querying Over hierarhical data

  1. Find all sub-ordinates of ITManager-A directly or indirectly (i.e., C D G I J K L M N O P)

    WITH cte_Person AS (………do as above ………….)
    SELECT Name FROM cte_Person
    WHERE ([Root] = N'1') AND (Manager IS NOT NULL)
  2. Find all sub-ordinates of FinanceManager(under E i.e. H)

    WITH cte_Person AS (………do as above ………….)
    SELECT Name FROM cte_Person
    WHERE ([Root] = N'2') AND (Manager IS NOT NULL)
  3. Find all persons working under Asst.ITManager(Under G => I J K L M N O P )

    WITH cte_Person AS (………do as above ………….)
    SELECT Name FROM cte_Person  
    WHERE ([Root] = N'1') AND ([Level] > 2)
For the sake of siplicity;
  1. We omit CTE definition in examples; Just copy it from above example and apply or you can define CTE in separate View and access in your query.
  2. In WHERE-CLAUSE we use constant value as [Root]=1, instead use sub-query to get root value;

5. Conclusion

CTE is a new feature introduced in SQL Server 2005. It allows you to hold the result-set temporarily for immediate Sql Query. CTE is replicable with Derived table or inline-view. The beauty of CTE is that it is non-persistent and auto-disposable object; it does not write any schema information or user data to disk. The other awesome advantage of CTE is that recursion; you can use recursive-CTE to navigate hierarchical data reliability. In my opinion, CTE is desirable when
  1. You need to hold result set temporarily for a single query only.
  2. Your query is large and contains multiple joins.
  3. You need to navigate the hierarchical data;
  4. You need to Self-Join the Sub-query data;

SqlTutorial-1 : Lesson-5 : Class-1 : Part-2
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Advanced-SELECTs: Pivot / UnPivot operator

Microsoft SQL Server Training Online Learning Classes Advance SELECT Operations Pivot UnPivot operators

Pivot and Unpivot are cross-tabular operators that transform (or rotate) a table column into rows or vice versa respectively.
INDEX:
  1. PIVOT
  2. UnPIVOT
  3. Advanced PIVOT and UNPIVOT Examples
  4. Feature Article related to PIVOT and UnPIVOT

  • Pivot and Unpivot are introduced in SQL Server 2005 that performs Cross-Tabular Transformation i.e., transforms a column value into row values or vice versa
  • Main usage these operators is to review cross-tabular analysis report.
  • Unpivot is reverse operation of PIVOT but not exact always; i.e., In Some cases it is possible that unpviot generates exact reverse of pivot, in some cases it is not, the reason is the aggregation of values while pivoting. Pivot selects aggregate value; see syntax; Therefore, If the column value of a table to be pivot assigned with multiple values; then Unpivot is would not reverse of PIVOT.
  • You can use Pivot and Unpivot simultaneously in one select query.


1. PIVOT Operator

Pivot Operator transforms a single-column into a row; For multiple column-transformation use multiple pivots operations simultaneously.

Syntax:

SELECT <non-pivoted column> ,
     [PivotedColumnValue1] AS <column name> ,
     [PivotedColumnValue2] AS <column name> ,
        ...
FROM
     ( <SELECT query that produces the data> ) AS < alias srcTable >
 PIVOT
     ( <aggregation function>( <column being aggregated> )
         FOR [<column values that will become column headers>]
         IN ( [PivotedColumnValue1] , [PivotedColumnValue2], ...   )
      ) AS <alias pvtTable>
[<optional ORDER BY clause>]

Example:

  1. Simple Example-1

    Table: SourceTable
    Cust Product Qty
    Kate Veg 2
    Kate Soda 6
    Kate Milk 1
    Kate Beer 12
    Fred Milk 3
    Fred Beer 24
    Kate Veg 3
    =>
    Table: ResultSet
    Product Fired Kate
    BEER 24 12
    MILK 3 1
    SODA NULL 6
    VEG NULL 5

    SELECT PRODUCT, FRED, KATE
    FROM (
                        SELECT PRODUCT, CUST, QTY
                        FROM Product
                ) AS SrcTable
    PIVOT(
                        SUM(QTY)
                        FOR CUST IN (FRED, KATE)
               ) AS pvtTable
    ORDER BY PRODUCT

  2. Simple Example-2

  3. Table: SoruceTable
    EmpId Cid Value
    101 C1 Xyz
    101 C2 Hyd
    101 C3 10000
    102 C1 Abc
    102 C2 Sec
    103 C3 15000
    =>
    Table: PIVOT ResultSet
    EmpID C1 C2 C3
    101 Xyz Hyd 10000
    102 Abc Sec 15000
    ... ... ... ...

    Select EmpID, C1,C2,C3
    FROM (Select Empid,Cid, Value
                  FROM Employee)
    PIVOT( Max(Value) FOR Cid IN (C1,C2,C3) )

    PivotData column is of String type, therefore use MAX()/MIN() function. SUM()/AVG() are invalid over Stringtype



  • Pivoting plays over only 3-columns data



  • 2. UnPIVOT

    UnPivot transforms table rows into columns.
    • It is reverse operation to PIVOT but not exact always;
    • Syntactically, UnPivot is similar to PIVOT except it does not use aggregate function and begins with UnPIVOT keyword.

    Syntax:

    SELECT <non-pivoted column> ,
         [PivotedColumnValue1] AS <column name> ,
         [PivotedColumnValue2] AS <column name> ,
            ...
    FROM
         ( <SELECT query that produces the data> ) AS <alias for the source query>
    UNPIVOT
         (  <columnName >
             FOR [<column that contains the values that will become row headers>]
             IN ( [PivotedColumnValue1] , [PivotedColumnValue2], ...   )
          ) AS <alias for the pivot table>
    [<optional ORDER BY clause>]


    Examples

    1. Unpivoting following table

      Table: Source Table
      Vendor
      Id
      Emp1 Emp2 Emp3 Emp4 Emp5
      1 4 3 5 4 4
      2 4 1 5 5 5
      3 4 3 5 4 4
      4 4 2 5 5 4
      5 5 1 5 5 5
      =>
      Table: UnPIVOT - ResultSet
      Vendor
      Id
      Employees Order
      1 Emp1 4
      1 Emp2 3
      1 Emp3 5
      1 Emp4 4
      1 Emp5 4
      2 Emp1 4
      2 Emp2 1
      2 Emp3 5
      2 Emp4 5
      2 Emp5 5

      SELECT VendorID, Employee, Orders
      FROM ( SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
                     FROM SourceTable) as srcTable
      UNPIVOT ( Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) ) as UnpTable
    2. UnPIVOT is not always reverse of PIVOT
      i.e.,Original Table != PIVOT + Unpivot Table.



    3. Advanced PIVOT and UNPIVOT Operations

    1. Matrix Transposition: Amxn to AT=Anxm


    For this transformation, you need to first unpivot then pivot the result. Because, Pivoting works only on three columns but here 6 columns are there and Unpivoting does not summarizes the data instead it expands the data.
    Let us consider a 5x5 matrix;
    Table1: MatrixTable(input)
    Table2: UnPivot ResultSet (Intermediate)
    Table3: PIVOT + UNIPOT Result-Set (OUTPUT)

    WITH ctePivotTable (RowId,Columns,[Values]) as
    (
                SELECT RowID, Columns, [Values]
                FROM       (  SELECT RowID, C1, C2, C3, C4, C5 FROM TableMatrix ) as SrcTable
                UNPIVOT (  [Values] FOR Columns IN ([C1],[C2],[C3],[C4],[C5]) ) as unPvtTable
    )
    Select [Columns], R1,R2,R3,R4,R5
    From  ( Select RowId, Columns, [Values] From ctePivotTable  ) as srcTable
    PIVOT  (  Max([Values]) for RowId in (R1,R2,R3,R4,R5)    ) as pvtTable ;


    4. Feature articles related to PIVOT and UnPIVOT

    1. How I Remember PIVOT and UNPIVOT Operation
    2. How can we PIVOT two columns simultaneously

    SqlTutorial-1 : Lesson-5 : Class-2
    Microsoft SQL Server Training:
    Free Online-Learning Classes (OLC) for T-SQL
    Advanced-GROUPING-BY:
    COMPUTE, ROLLUP|CUBE, PARTITION, NTILE

    INDEX
    1. N-Tile()
    2. Over ( PARTITION-clause) 
    3. COMPUTE-BY Statement
    4. GROUP BY with CUBE | ROLL-UP
    5. GROUPING()


    1. N-TILE

    It creates N-Number of groups by deviding the resultset into N-number of groups. Each group is identified by a serial number.

    Syntax:

    NTILE(integer_exp) OVER( [ <partition_by_clause> ] < order_by_clause >)

    Examples

    SELECT region, NTILE(4) 
       over(order by region)
    FROM Employee
    SELECT region, NTILE(3)
      over(order by region)
    FROM Employee
    SELECT region, NTILE(1)
       over(order by region)
    FROM Employee
    OUTPUT OUTPUT OUTPUT
    Region Region Region
    East 1 East 1 East 1
    East 1 East 1 East 2
    North 1 North 1 North 3
    North 2 North 2 North 4
    North 2 North 2 North 5
    South 3 South 2 South 6
    South 3 South 3 South 7
    West 4 West 3 West 8
    West 4 West 3 West 9


    NTILE(No.of rows)= Row_NUMBER(), see NTILE(1) example of above.

    2. Over (PARTITION-clause)

    Using Over(Partition) clause you can do two thing:
    1. You can apply Aggregate functions on different columns simultaneously in single SELECT.
    2. You can SELECT columns with Aggregate functions without GROUP-BY clause.

    Example:

    USE AdventureWorks;
    GO

    SELECT SalesOrderID, ProductID, OrderQty ,
       SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total,
       AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg" ,
       COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count" ,
       MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min" ,
       MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderID IN(43659,43664);

    OUTPUT :
    Microsoft SQL Server Training Online Learning Classes Advance GROUPING COMPUTE ROLLUP CUBE PARTITION NTILE



    3. COMPUTE-BY Statement

    • It adds summary report computed with aggregate function to result set.
    • Used to generate additional summary at the end of the result set.
    • The beauty of COMPUTE is that you can nest multiple aggregate function in single statement. e.g,  COMPUTE MIN(SUM(salary));

    1. The result of Query containing COMPUTE is invisible using Visual Studio, use Sqlcmd instead.
    2. BY clause is optional, if present then ORDER BY clause with same expression must exists, otherwise it is error.
    3. COMPUTE is applied to result of all aggregate function. i.e, to all groups. Therefore, you can use it to find Minimal/maximum group.
    4. COMPUTE BY is applied to each group(i.e, within group) not all groups(i.e, not among all group). Therefore, you can use it to find sub-totals of each groups.>/li>

    Syntax:

    COMPUTE { Aggregate Function1} ( expression ) } [ Ag.Func2 ,...n ]
            [ BY expression [ ,...n ] ]

    Examples:

    1. Find sum and average of salaries of employees.

      SELECT id, first_name, last_name, salary,
                  start_Date, region, city, managerid
      FROM Employee
      COMPUTE SUM(salary), AVG(salary)
      GO

      OUTPUT:

      Microsoft SQL Server Training Online Learning Classes Advance GROUPING COMPUTE ROLLUP CUBE PARTITION NTILE
    2. It is similar to SELECT SUM(salary), AVG(salary). But not followed by Query res
    3. Find the smallest salaried region

      SELECT SUM(salary) AS Expr1
      FROM Employee
      GROUP BY region
      COMPUTE MIN(SUM(salary))
      OR
      SELECT MIN(t1) As Expr1,
      FROM (SELECT SUM(salary)
           FROM Employee
           GROUP BY region)  

      OUTPUT:

      Invalid Solutions
      1. Invalid-1: The following raises an error because Aggregate function cannot take aggregate function as parameter

        SELECT Min(Sum(Salary)) -- Error.
        From Employee
        Group By Region
      2. Invalid-2: The following returns Minimum value in each region. But not minimal valued region in all regions.

        SELECT MIN(salary)
        From Employee
        Group By Region
    4. Find the sub-totals with details for each regions. (using COMPTUE BY )


      COMPUTE BY is applied to each group not all groups as whole. Note that it must be followed by ORDER BY clause with same column.

      SELECT salary, region
      FROM employee
      ORDER BY region
      COMPUTE SUM(salary) by region

      OUTPUT



    4. Group By with CUBE| ROLLUP
       ( a.k.a Grouping Sets Operators )

    • CUBE and ROLLUPs are extentions to GROUP-BY that adds additional Summary to result-set.
    • When SELECT query contains GROUP with CUBE | ROLLUP, it returns two result sets; one result set for query execution output and second for Summary.  
    • The ouput is similar to writing two queries by combing using UNION-ALL, but SETS-Operators are more efficient to write.

    Syntax:

    GROUP BY expression[,..n] [WITH {CUBE | ROLLUP} ]

    Examples :

    Let us consider a M:M:M relationship. E.g, Employees:Projects:TeamLeader where
    1. Each project have multiple employees, and each employee works in multiple project.
    2. Each project is handled by multiple Team Leaders and each Team leader lead multiple projects.



    A: CUBE:

    CUBE: Appends summary report of all possible combinations of values in aggregate columns.
    1. Find TOTAL and SUB-TOTAL for each Project, Employee (All combinations PROJECT: EMPLOYEE)

      Query OUTPUT
      SELECT Project,  [Emp Name],
        COUNT(*) [No of Employees]
      FROM Table1
      GROUP BY  Project, [Emp Name] WITH CUBE

      =

      SELECT Project, [Emp Name], COUNT(*) FROM Table1
      GROUP BY Project, [Emp Name]
      UNION ALL 
      Select NULL, NULL, Count(*) FROM Table1
      UNION ALL
      Select NULL, [Emp Name], Count(*) FROM Table1;
      GROUP BY [Emp Name]
    2. Find TOTAL and SUB-TOTAL for each Project, Employee, TeamLeader Groups.

      SELECT Project, [Emp Name], [TeamLeader], COUNT(*) AS [No of Times]
      FROM Table1
      GROUP BY Project, [Emp Name] , [TeamLeader] WITH CUBE

      OUTPUT

    B: ROLL UP

    • Similar to CUBE it appends summary report to resultset, but works with only single combination of values specified in function.
    • It enables a SELECT statement to calculate multiple levels (hierarchical) of aggregate value (e.g. subtotals) across a specified group of dimensions. and whole-level of aggregate value(e.g., grand total.)
    1. Find TOTAL and SUB-TOTAL no. of Employees in each Project ( ie., Single group Combination)

      SELECT Project, [Emp Name], COUNT(*) AS [No of Employees]
      FROM Table1
      GROUP BY Project, [Emp Name] WITH ROLLUP

      =

      Select Project, EmpName, Count(*) FROM Table1
      GROUP BY Project, EmpName
      UNION ALL
      Select NULL, NULL, Count(*) FROM Table1

      OUTPUT
    ROLLUP simply summarizing data hierarchically whereas CUBE summarizing as whole.


    5. GROUPING - Row indicator for CUBE | ROLLUPs

    • It differentiates stored values (NULL or Not-NULL) from NULL values created by ROLLUP/CUBE NULL.
    • It returns 1 if row is ROLLUP(/CUBE) have NULL-value otherwise 0.
    • It identifies in which rows Total and Sub-totals.
    • The main usage is to do decoration to ROLLUP/CUBE summery result.

    Examples:

    Example-1: Show what changes are made by GROUPING()verb

    Without GROUPING() With GROUPING()

    SELECT Project, [Emp Name]
    FROM Table1
    GROUP BY Project, [Emp Name]
    WITH ROLLUP


    SELECT Project, GROUPING([Emp Name])
    FROM Table1
    GROUP BY Project, [Emp Name]
    WITH ROLLUP



    OUTPUT:
    Project Emp Name
    Project1 e1
    Project1 e2
    Project1 e3
    Project1 e4
    Project1 NULL
    Project2 e1
    Project2 e2
    Project2 NULL
    Project3 e1
    Project3 e4
    Project3 NULL



    OUTPUT:
    Project Emp Name
    Project1 0
    Project1 0
    Project1 0
    Project1 0
    Project1 1
    Project2 0
    Project2 0
    Project2 1
    Project3 0
    Project3 0
    Project3 1

    Example-2: Decorating with GROUPING()

    SELECT
            CASE WHEN GROUPING([Project])=1 
                THEN 'All Projects'
                ELSE [Project ] END,
           CASE WHEN GROUPING([Emp Name])=1 
                THEN 'Total Emps'
                ELSE [Emp Name] END AS [Employees],
           COUNT(*) AS [No of Employees]
    FROM Table1
    GROUP BY Project, [Emp Name] WITH ROLLUP

    OUTPUT
    Project Emp Name Employees
    Project1 e1 1
    Project1 e2 1
    Project1 e3 1
    Project1 e4 1
    Project1 Total 4
    Project2 e1 1
    Project2 e2 1
    Project2 Total Emps 2
    Project3 e1 1
    Project3 e4 1
    Project3 Total Emps 2
    All Projects Total Emps 8

    SqlTutorial-1 : Lesson-9 : Class-2
    Microsoft SQL Server Training:
    Free Online-Learning Classes (OLC) for T-SQL
    Sql-INDEX Operations:
    Creation, Altration, Deletion and Optimization

    Microsoft SQL Server Training Online Learning Classes INDEX Creation Deletetion Optimizations

    Index is a performance optimization technique that speeds up the data retrieval process. It is a persistent data structure (Key-Pointer) that associated with a Table (or View) in order to increases performance during retrieving the data from that Table (or View).

    INDEX
    How-Do-I:
    1. CREATE INDEXes on Table
      • Syntax and Description
      • CREATE Simple INDEX
      • CREATE Indexes with INCLUDED columns
      • CREATE Indexes with Fill-factors and Pad-Index
      • CREATE Indexes - Advanced Examples
    2. CREATE Indexes On Views
    3. ALTER Index
    4. Drop INDEX
    5. Renaming INDEX;
    6. Index Hint.
    7. Interrogating INDEXe;


    1. CREATE INDEXes on Table

    1.1. Syntax and description

    CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX
      Index_Name 
        ON "object" ( column_name [ ASC | DESC ] [ ,...n ] )
        [ INCLUDE ( column_name [ ,...n ] ) ]
        [ WITH "relational_index_option" [ ,...n ] ]
        [ ON { filegroup_name | "default" } ]

    Where:

    1. The default options are NON-UNIQUE and NON-CLUSTERED.
    2. UNIQUE: creates unique index on table/view. You cannot create unique index on duplicate columns and NULL valued columns. Because multiple NULL values are considered as duplicate.
    3. CLUSTERED | NONCLUSTERED: Defines kind of INDEX you want to be created.
    4. INCLUDE(Column[,…n]): Adds the non-key columns to the leaf level of the non-clustered index. The maximum of 1,023 non-key columns you can include. Columns cannot be used simultaneously as both key and non-key. Unlike search key, you can include any data type column.
    5. WITH "relational_index_option": The following are relational_index_option.
      1. PAD_INDEX={ON|OFF}: Determines whether or not free space is allocated to non-leaf nodes. The default is OFF, means “nodes/pages may full while constructing index structure”. If ON, then nodes contains free space as specified by fillfactor.
      2. FILL FACTOR=(1-100%): PAD_INDEX determines whether or not free space is needed. Fill Factor determines the amount of free space. Therefore, these are dependent on each other. If PAD_INDEX=OFF, then Fill factor value ineffective. Similarly PAD_INDEX=ON, Fill Factor=0, then no padding at all. The default is 0.Fill factor values 0 and 100(0=100). Fill-factor setting applies only when the index is created or rebuilt (restructured). For insertion, deletion fill-factor is not applicable. Note that non-leaf Nodes/pages never less than two records. Fill-factor value follow this condition.
        The main usage of PAD_INDEX with FILL FACTOR is to minimize the tree reorganization and redistribution while insertion and deletion operations.
      3. SORT_IN_TEMPDB={ON | OFF}: Specifies whether to store sort result in tempdb. Advantage is performance and disadvantage is extra amount of disk space.
      4. IGNORE_DUP_KEY={ON | OFF} : Disables/enables uniqueness ability on unique index.
      5. DROP_EXISTING={ON| OFF}: ON ; drops the existing index when you create an index with existing name. Two indexes with same name cannot be exists, therefore existing is deleted when ON otherwise new index creation is failed. Use it as alternative to ALTER INDEX.
        A clustered and a non-clustered index with same name is also not possible.
      6. ONLINE={ON | OFF}: ( It is available in SQL 2005 enterprise edition). Specifies while index operation (creation, structuring), whether or not you want to lock the base tables and associated indexes. If ON, tables are available for queries and data modification during the index operation.
      7. MAXDOP: Maximum Degree Of Parallisms; works with multiple CPUs.
      8. ALLOW_PAGE_LOCKS={ON|OFF}, ALLOW_ROW_LOCKS={ON | OFF}


    1.2. CREATE INDEXes - Simple Examples

    1. Creating simple non-clustered and non-unique INDEX.

      CREATE INDEX IX_VendorID ON Vendor(VendorName);
       // OR //
      CREATE NONCLUSTERED INDEX IX_VendorID ON Vendor(VendorName);
    2. Creating simple CLUSTERED INDEX.

      CREATE CLUSTERED INDEX IX_VendorID ON Vendor(VendorID);
    3. Creating index with sort direction.

      CREATE NONCLUSTERED INDEX NI_Salary ON Employee(Salary DESC)
    4. Creating index on composite column.

      CREATE INDEX NI_YourName ON Employee(ID, First_Name)
      //OR//
      CREATE NONCLUSTERED INDEX ON Employee(ID ASC, First_name ASC)
    5. Creating index UNIQUE non-clustered index.

      CREATE UNIQUE NONCLUSTERED INDEX I_PkId ON Employee(Eid);
    6. Enforcing uniqueness on non-key columns. You cannot insert duplicate values in First_Name column

      CREATE UNIQUE NONCLUSTERED INDEX U_I_FirstName ON Employee(First_Name)


    1.3.CREATE Indexes with INCLUDED columns

    1. Creating index with included columns.

      Last_name non-key column is added to leave node of the index tree. It increases the performance for SELECT ID, First_Name, Last_name FROM Employee.
      CREATE INDEX NI_YourName ON Employee(ID, First_Name)
      INCLUDE (Last_name)

    1.4. CREATE Indexes with Fill-factors and Pad-Index

    1. Creating Index with 50% Padding (to minimize tree reorganization while insertion)

      CREATE NONCLUSTERED INDEX I_ID ON Employee(ID,First_Name) WITH (FILLFACTOR=50, PAD_INDEX=ON)

    1.5. CREATE INDEXes - Advanced Examples

    1. Creating an index in a file-group.

      1. Define file group

        ALTER DATABASE YourDatabase ADD FILEGROUP FG2
      2. Attach data file to file group.

        ALTER DATABASE YourDatabase ADD FILE( NAME = AW2,FILENAME = 'c:\db.ndf', SIZE = 1MB) TO FILEGROUP FG2
      3. Define the INDEX on that file-group.

        CREATE INDEX I_IndexName ON TableName (ColumnName)ON [FG2]
      4. You can define Database file and non-clustered Index in different file groups.
    2. Keep the intermediate index results in Tempdb.

      CREATE NONCLUSTERED INDEX NI_FirstName ON Employee (First_name) WITH (SORT_IN_TEMPDB = ON)
    3. Disable UNIQUENESS on UNIQUE index

      CREATE UNIQUE INDEX IMy_Index ON Employee(name)
      WITH (IGNORE_DUP_KEY=ON);
      -- Now, youc an insert duplicate values into ‘name’ column
    4. Disable page locks. Table and row locks can still be used.

      CREATE INDEX NI_FirstName ON Employee (First_Name)
      WITH (ALLOW_PAGE_LOCKS=OFF)

    2. CREATE Indexes On Views

    1. Creating an index on View

      1. Define a view

        CREATE VIEW vEmployee WITH SCHEMABINDING AS SELECT Id, first_name FROM dbo.Employee
      2. Define Index on View

        CREATE UNIQUE CLUSTERED INDEX IXvwBalances ON vEmployee(Id)


    3. Altering INDEXES

    Syntax:

    ALTER INDEX { index_name | ALL } ON "object"
    {    REBUILD [ WITH ( "rebuild_index_option" [ ,...n ] ) ]
          | DISABLE
          | REORGANIZE [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
          | SET ( "set_index_option" [ ,...n ] )
    }

    Where

    • "rebuild_index_options" :
          PAD_INDEX|FILLFACTOR | SORT_IN_TEMPDB IGNORE_DUP_KEY |
          ONLINE | ALLOW_ROW_LOCKS | ALLOW_PAGE_LOCKS | MAXDOP
    • "Set_index_option" :
      ALLOW_ROW_LOCKS|ALLOW_PAGE_LOCKS | IGNORE_DUP_KEY|STATISTICS_NORECOMPUTE

    Examples:

    1. Disabling an existing index.

      ALTER INDEX NI_Salary ON Employee DISABLE
      -- Enabling
      ALTER INDEX NI_Salary ON Employee REBUILD
    2. Disabling all indexes.

      ALTER INDEX ALL ON Employee DISABLE
      -- Enabling
      ALTER INDEX ALL ON Employee REBUILD
    3. Disabling primary key constraint using ALTER INDEX.

      ALTER INDEX PK_DeptID ON Department DISABLE
      -- Enabling
      ALTER INDEX PK_Dept_ID ON Department REBUILD
    4. Altering INDEX using CREATE INDEX with DROP_EXISTING option.

      CREATE NONCLUSTERED INDEX NCI_FirstName ON Employee(ID, First_name) WITH (DROP_EXISTING = ON)
      -- It deletes existing NCI_FirstName index and defines new index.
    5. Rebuild(re-organize tree) an index.

      ALTER INDEX PK_Employee ON Employee REBUILD;
    6. Alter all indexes with padding.

      ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80,PAD_INDEX=ON,SORT_IN_TEMPDB = ON);
    7. Alter and index for disabling Uniqueness.

      ALTER INDEX My_Index ON Employee SET (IGNORE_DUP_KEY=ON, ALLOW_PAGE_LOCKS=ON)
    8. Disable page and row locks. Only Table locks can possible.

      ALTER INDEX NI_FirstName ON Employee
      SET (ALLOW_PAGE_LOCKS=OFF,ALLOW_ROW_LOCKS=OFF )

      ALTER INDEX NI_FirstName ON Employee
      SET (ALLOW_PAGE_LOCKS=ON,ALLOW_ROW_LOCKS=ON )

    4. Dropping INDEXES

    Syntax:

    DROP INDEX "index_name" ON Table_Name // OR // DROP INDEX Table_Name.IndexName
    The DROP INDEX statement does not deletes indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and corresponding index, use ALTER TABLE with DROP CONSTRAINT clause

    Examples:

    1. Dropping an explicitly created index.

      DROP INDEX i_empno ON employee
      // OR //
      DROP INDEX employee.i_empno
      // OR //
      IF EXISTS(SELECT name FROM sys.indexes WHERE name= ‘I_empno’) DROP INDEX i_empno ON employee
    2. Dropping multiple indexes of multiple tables.

      INDEXES created by defining PRIMARY KEY or UNIQUE key constraints. ALTER TABLE Employee DROP CONSTRAINT PK_Employee_EId WITH (ONLINE=ON)
    3. Dropping implicitly created index.

      DROP INDEX i_empno ON HumanResource.Employee, i_rno ON Institution.Sudents


    5. Renaming INDEX

    Using system-defined Stored Procedure sp_rename you can rename Tables and their columns, Databases, Indexes etc.

    Syntax:

    sp_rename 'OldName' , 'newName', 'object_type'
    Where Object_type= Unspecified(Table) | COLUMN | DATABASE | INDEX | OBJECT | STATISTICS

    Examples:

    1. Renaming INDEX MyIndex1 with MyIndex2

    EXEC sp_rename N'dbo.MyIndex1', N'MyIndex2', N'INDEX';


    6. INDEX Hint

    INDEX Hint eforces SQL-Query Optimzier to use the specified Indexes while executing that query. There are two ways you can integrate INDEX hints to your query.

    Way-1: Inline-INDEX hint using WITH-Clause

    SELECT t1.Id
         FROM Table t1 WITH (INDEX (Ind_Table1_ColName1))
              INNER JOIN Table2 t2 WITH (INDEX(Ind_Table1_ColName2))
                   ON tt1.ID = t2.ID

    Way-2: Appending-INDEX hint to query using OPTION-Clause

    SELECT t1.Id
    FROM table1 t1  INNER JOIN table2 t2 ON t1.Id= t2.Id
    OPTION (  
              TABLE HINT(t1, INDEX(Ind_Table1_ColName1)),

              TABLE HINT(t2, INDEX(Ind_Table1_ColName2) )
    )



    7. Interrogating Indexes INDEXes

    1. To see all indexes available in database

      Select * from sys.Indexes
    2. To all Indexes over a table "Customer"

      EXEC sp_helpindex Customer

    SqlTutorial-1 : Lesson-9 : Class-1
    Microsoft SQL Server Training:
    Free Online-Learning Classes (OLC) for T-SQL
    Overview of Sql-INDEX Operations and Optimizations


    Index is a performance optimization technique that speeds up the data retrieval process. It is a persistent data structure (Key-Pointer) that associated with a Table (or View) in order to increases performance during retrieving the data from that Table (or View).
    D atabase-Table is good enough to store business data but it is a lack of meta-information needed to search its data more quickly and efficiently. This behavior of table might not affect Search-Performance and might be ignorable if either the table contains minimal set of records, or you always querying table without WHERE-filters or both. However, in real-world databases none of the scenario is supportive. There could be thousands of records in a table, and you need to extract the information with complex queries having various WHERE-filters including Sub-queries WHERE-filters too. This will take a long time and causes a significant performance impact. Therefore, Indexes are come into the picture. You can create about 250 indexes over a single table based on columns which frequently used in WHERE-filters of queries. These columns called Search-Keys.

    Microsoft SQL Server Training Online Learning Classes INDEX Overview,  Optimizations , Clustered and Non-clustered Indexes
    A table without any Indexes stores the data into an un-organized memory structure called Heap. When you execute a query against heap even using WHERE-filter, SQL-Server searches entire table from 1st row to last-row sequentially. This searching process is called Table-Scan where as the search over against an Indexed Table called Index Lookups. The queries that use Indexed columns in WHERE-filter are called Covering Queries.

    Indexes in database are much similar to Indexes in the Book. They describe book contents in two ways: Topic-wise (called Book-Contents put at front of the book) and Keyword-wise (called Book-Index put at back of book): For example, if you want to find out the pages of book where particular topic is discussed, then refer the Index which contains topic name in alphabetic order along with page Numbers. A book-page can contain multiple topics or a topic can spread across multiple book-pages. Similarly a databases-page (a.k.a., bucket) can contain multiple records or a single record can spread across multiple pages if too long, the maximum size of database-Page: 8kb and maximum-record size is based on data types of column e.g., only a single xml-type column can contain 2Gb of data.

    In databases there are two types of Indexes: Clustered and Non-Clustered Indexes. The clustered index is similar to Topic-Wise contents of the book in which topics are sorted sequentially in order of the page numbers; here Table-rows are sorted in Clustered Index-Key order, typically Primary Key order. A book can organize the pages in one sequential order similarly a table organizes their records according to a Clustered-Index search-key order. Hence, a table can have at-most one Clustered Index.

    The Database-Index also contains Search-keys along with Pages where records are available. Database-page is a collection of records access as whole for retrieval and persisting data to and from databases.

    Database-Index contains Keys [column(s) of Table or View] and these Keys stored in a tree-like structure called Balanced-Tree (B+ -Trees). This B-Tree organization of Keys gives two advantages:
    1. Key-by-Key search instead of Record-by-record search. 
    2. Dynamic search instead of linear or sequentially search.
    The primary advantage as well as disadvantage of Indexes is performance. Because, one-hand indexes speed ups data retrieval from database, but on other-hand they slow-downs INSERT, DELETE and UPDATE operations since these operations should be reflect on both table as well as Index-structures. By default SQL server creates Clustered Index on primary key, this is the reason why you can’t modify he primary key-value. The reasoning of additional index is depends on 2-factors:
    1. Frequency of columns that uses in SELECT-WHERE-clauses: 
    2. Frequency of columns that modified or deleted UPDATE-WHERE and DELETE-WHERE.
    Note that frequency of large data type columns such as Binary-data type, Xml data type columns might be zero. Therefore, it is not advisable to create index on such types of columns.

    The key-role of DBA is to determine and design proper Index that should optimize for your database. DBA needs to balance the between frequency of Data-Retrieval and Data-Modifications.


    INDEX
    Overview
    1. What are indexes?What are their advantages, disadvantage and Restrictions
    2. Index Aspects: Cans and Can'ts of Indexes
    3. Types: Clustered Vs Non-Clustered
    4. How Indexes help PERFORMANCE Tuning and When should Indexes  avoided?
    5. FAQs


    I. Overview

    1. INDEXes; Avantages, Disadvantage and Restrictions

    a) Definition

    Index is a performance optimization technique that speeds up the data retrieval process. It is a persistent data structure (Key-Pointer) that associated with a Table (or View) in order to increases performance during retrieving the data from that Table (or View).

    b) Advantages and disadvantages

    The main advantage as well as dis-advantage of Index mechanism is PERFORMANCE. Because, it speed ups the data retrieval process but slow-downs the data modifications process performance. Updating a table with Indexes takes more time than updating without Index, because SQL server needs an additional updates to INDEX entry also. Therefore, before creating explicit index you have to balance the frequency of Data-Retrieval and Data-Modifications on Search-key.

    Advantages:

    Indexes gives two advantages:
    1. Performance optimization i.e., to speed up data search in database. 
    2. To Enforcing uniqueness on non-key columns. i.e., A column which is neither a PRIMARY key nor a UNIQUE key, can be restricted as it can contain only distinct values.

    Disadvantages:

    1. Performance: Adding indexes (both clustered and non-clustered) slow down insert, delete, update operations as data has to be updated in indexes and indexes possibly re-structured.
    2. Extra Memory consumption: The amount of space that they require will depend on the columns in the index, and the number of rows in the table.

    c) Restrictions

    • A table can have maximum 250 index, in which 1-clustered and 249 non-clustered.
    • Up to 16-columns can be combined into a single composite index key.
    • You can INCLUDE non-key columns from 1 - 1,023 columns to non-clustered index.
    • The max size of combined/single column is 900 bytes(not 9kb) and size(page)=8060 bytes(8kb).
    • You cannot create index on large object (LOB) data type columns such as ntext, text, varchar, binary etc. However, you can INCLUDE these columns to non-clustered indexes. For indexing on Text, ntext, and image etc use FULL TEXT INDEXES instead NORMAL INDEXES.


    2. Index Aspects: Cans and Can'ts of Indexes

    A) Basic Aspects of Indexes :

    • When an Index is created, it first sorts the records of tables and then assigns ROWID to each row.
    • Indexes also enforces UNIQUE constraints, i.e., without using UNIQUE constraints you can make a column as it can contains only distinct values. This can be done by creating UNQIUE-index on that column.
    • Indexes are persistent Database Objects. Therefore, they allocate memory and stored by a global visible name.
    • Indexes are independent and isolated to their associated tables or views. Therefore, you can create additional Indexes on table and you can alter, rebuild, reorganize and even delete Indexes without affecting base tables. But when you delete base table or view, all associated indexes get deleted automatically.
    • SQL-Server uses Indexes with all DML operations that use WHERE-Filter e.g., SELECT-FROM-WHERE, UPDATE-SET-WHERE and also DELETE-FROM-WHERE statements.
    • SQL-server perform full-table-scan instead of Index-lookups when DML operations not uses WHERE-filter SELECT-FROM, UPDATE-SET, DELETE-FROM. 
    • Updating a table with Indexes takes more time than updating without Index, because SQL server needs an additional updates to INDEX entry also. Therefore, before creating explicit index you have to balance the frequency of Data-Retrieval and Data-Modifications on Search-key.
    • You can create at most 250-indexes on a view or table. 1-Clustered and 249-Non-Clustered.
    • Index is organized in tree-like structure called B-Trees which always maintains same depth to all pages.

    B) What INDEX-CANs:

    • Indexes are created against Table as well as against Views and Global Temporary Tables.
    • An index can be created on empty as well as non-empty table.
    • You can create duplicate Indexes, i.e., two Indexes on same column. This is not recommended and must be analyzed and avoided as this regrets performance significantly.
    • Indexes (both clustered and Non-Clustered) can be created on either single column or composite columns even having Xml columns or binary etc data types but not recommended. However, Sql Server introduces a new type of indexing called Xml-Indexing for xml type-of columns.
    • You can create clustered and non-clustered index on same single column simultaneously. In this case, Non-clustered index becomes useless and SQL server always uses clustered index instead.
    • You can create Clustered Index and Primary Key on different columns. In this case Primary Key uses Non-clustered Indexes, but the condition is Clustered
    • Index must be created before adding Primary key to table.
    • You can define Index of Table in different databases. For this use fully-qualified database name while defining Indexes:

      [database_name.[ schema_name ].| schema_name. ] table_or_view_name

    C) What INDEXes-CAN'Ts :

    • Two indexes with same name even against different tables are not allowed.
    • It is not possible that a table having primary key but not Clustered Index but reverse is possible i.e., a table can exists with Clustered Index but without primary key.
    • A single Index cannot be associated with multiple tables. For example, you cannot an Index with composite columns taking from different tables.
    • You cannot create index on Non-key columns i.e., the Non-primary-Key or Non-Unique-Key column are invalid for creating indexes. However, you can Include those columns.
    • For a view, you cannot create Index on View columns defined by Expression or fx(n).

    3.INDEX-Types : Clustered Vs Non-Clustered

    There are two tyeps of INDEXes : Clustered and Non-Clustered.
    By default Clustered Index is created when you add Primary Key to table  and Non-clustered Indexes are created when you add Unique-keys to tables.

    A. Clustered INDEX

    1. Clustered index organizes the table records in sequential order based on its key. (Typically, Index key is primary key, however DBA can select other unique-keys for this while defining clustered index).
      You can verify this as follows:
      • Suppose, If you want insert the records in descending order (default ASC); then
        create Clustered index on primary key with DESC order.
      • If you don’t want the order and need to insert the records in ad-hoc fashion,
        just don’t create Clustered index and create table without Primary key.
    2. SQL Server creates Clustered INDEX automatically if not exists, when you add Primary Key to Table.
    3. Bottom, or leaf level of the clustered index structure contains the actual data rows of the table
    4. A table can contain at most one Clustered Index.
    5. Clustered indexes do not add included columns. Because, it implicitly adds all columns of the base table. Clustered indexes are defined within the table itself not outside of the table.

    B. Non-Clustered INDEX

    1. A non-clustered index does not enforces order of table rows, instead it adds a additional pointers to actual data.
    2. SQL server 2005 allows up to 249 non-clustered index per table. Whereas SQL Server 2008 allows up to 999 non-clustered indexes per table.
    3. Each non-clustered index can contain upto 1023 included columns.
    4. Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
    5. Foreign keys should be non-clustered.
    6. It is similar to an index in the back of a book.

    C. Clustered Vs Non-Clustered

    The main difference between Clustered and Non-Clustered Indexes are based on following:
    1. Physical Sorting: The clustered index sorts data sequentially based on index-key whereas Non-Clustered Index does not instead it refers the data according to secondary-keys.
    2. Location : The clustered must be in same file-group/page where associated table avialable whereas Non-clustered index can exists in different file-group/page even in different databases from its associated Table.
    3. Existance: A table can contain at most one Clustered Index whereas Non-clustered indexes can be 249/SQLServer-2005 and 999/SQLServer-2008
    4. Performance and priority: Clustered index performs faster than non-clustered index, therefore if both clustered and non-clustered index are created on same column(or key), then SQL Server uses Clustered Index
    5. Data Availability:  As far as concerned to their structures, both clustered index and non-clustered indexes organizes their search-key values in B-Trees, but Clustered Index contain Pages in leaf nodes whereas Non-clustered Index contains references to pages instead of actual data.
    6. INCLUDED-columns: Clustered Index does not contains INCLUDED columns whereas Non-clsutered can contain in order to add Pages to its leaf-nodes directly.


    4. How Indexes help PERFORMANCE Tuning

    The main advantage as well as disadvantage of Indexing is Performance. There are two reasons that degrades the performance when Indexes are applied?
    • Frequent DML operations: , particularly Insert and delete operation. These operations cause to re-organize the tree, and updating nearly 249 index entries. 
    • Poor designing of indexes: It includes
      1. Creating indexes on frequently modifying columns. (It causes modifications to indexes).
      2. Defining un-necessary non-clustered indexes. (All are loaded and used un-necessarily) As a thumb-rule, create non-clustered indexes on columns that are frequently referenced in WHERE clause, JOIN and GROUP BY clauses.
      3. Creating index on large keys specially Char keys (As Each character get compared) to keep index keys Short and use possibly integer type columns)
        Clustered index is desired when you extract large result sets and Non-clustered index is used to access small amount of data.

    A: Use Clustered index when

    1. Choose the column which is infrequently modified for clustered index.Typically primary key. 
    2. Sequential access to a table. 
    3. Range of data using BETWEEN, >= & =<, >&< in WHERE clause.
    4. Frequently joining with other tables based on a particular column. 
    5. ORDER BY clauses or GROUP BY clauses.
    6. Clustered indexes are not advised to be used for columns that are updated frequently

    B: Use Non-clustered Index when

    1. Queries that do not return large result sets 
    2. Columns that frequently in WHERE clause with equality condition.
    3. If a table is updated often and queried less so, you should carefully consider the benefits and drawbacks of adding such an index.

    C: When should indexes be avoided?

    Some times indexes creates performance overheads. The following are situations when we don't want indexes no more.
    1. If your tables is small contains and contains minal no. of records
    2. If your table is frequently modified by large updates or insert operations.
    3. If your column NULL-cardinality is high i.e., contains multiple NULL values then Indexes not required.

    5. INDEX - FAQs

    1. What are Indexes? How they are different from Tables.

      Index is a performance optimization technique that speeds up the data retrieval process. The following are some difference between Tables and Indexes:
      1. Tables contains actual records whereas Indexes contains Tables meta-information in order to speed up data-retrieval
      2. A Table can have multiple INDEXes but an Index can associated with one and only one Table
      3. Deleting of Tables deletes all associated Indexes whereas reverse is not
    2. What Primary-Indexes (Clustered) and Secondary Indexes(Non-Clustered)

      See Clustered and Non-clustered indexes and difference between them as told in above section.
    3. How many Indexes can a table contain

      SQL-Server 2005 supports 250 indexes; 1-Clustered and 249-Non-Clustered. But SQL-Server 2008 supports 1000 Indexes; 1-Clustered and 999-Non-Clustered.
    4. What are Auto-generated Indexes

      The clustered Index on Primary-Key and Non-Clustered Indexes over Unique are autogenerated Indexes.
    5. Can we create primary-key and Clustered Index on different columns?

      Yes, we can create primary-key and clustered Index on different columns. For this you have to Create primary key with NON-CLUSTERED specification.

      Example

      CREATE TABLE Table1 (
           Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
           Col1 varchar NOT NULL
      )
    6. Main advantage of Index

      Performance tuning; See advantages and disadvantages section for details
    7. How Indexes are physically organized?What are B+ trees?

      Indexe-keys are physically organized as a tree-like structrue called B-Trees? B-Trees stands for Balanced-Tree, that maintains same depth to all its leaf nodes.
    8. What is fill factor? How can we minimize the index-tree re-organization

      Fill factor is the percentage of additional memory allocated to leaf-nodes of Index B-Trees. It is greate measure to avoid index re-rebuilt or re-organization while INSERTs and DELETEs performance. At down-side it allocates additional memory for each leaf-nodes.
    9. What is Heap, Table-Scan and Index-lookups.

      Heaps:
      Ad-hoc Memory allocation for table data which is un-organized and un-sorted. Typically the memory location for Tables havingno Clustered Index is called heap. Heap needs Table scan for data access.
      Table Scan: Process of searching through every row in the table (i.e., every page and records) is said to be Table scan. SQL Server performs table scan on in two situations:
      1. Heaps i.e., there are no Clustered-indexes on table.
      2. Your query contains no WHERE-filter e.g., SELECT * FROM TableName;
      Index Lookups: Index Lookups is the alternative process to Table Scan; in which either Clustered or Non-clustered index is used to search the desired records. The queries with WHERE conditions need Index Lookups to speed up the search process. Indexing allows efficient data lookups.
    10. What are Index-Coverage and Covering Query

      Index Coverage: A covering index is a special case where the non-clustered index itself contains required data field(s) and can return the data. This can be accomplished by adding required INCLUDED columns.

      Example

      CREATE INDEX Indx1_Emp ON Employee(ID) INCLUDE Employee(name)
      // The above index is desired for following query.
      Select name from Employee where Id=101;
      // But the above index is un-usable for query like
      Select * from Employee where Id=101;

      Covering query: A query that uses columns on which indexes are created. This scenario results in superior query performance.

    SqlTutorial-1 : Lesson-6 : Class-1
    Microsoft SQL Server Training:
    Free Online-Learning Classes (OLC) for T-SQL
    Sql-JOIN operations: INNER, OUTER, CROSS

    Microsoft SQL Server Training Online Learning Classes Sql JOIN Operations INNER OUTER CROSS

    JOINs are used to access the data that combined together from multiple tables simultaneously.
    INDEX
    1. Introduction 
    2. Types of JOINs ( INNER, OUTER, CROSS )
    3. INNER JOIN ( Default JOIN Type )
    4. OUTER JOIN (FULL | LEFT | RIGHT and default=None; Optional=OUTER Clause)
    5. CROSS JOIN
    6. DELETE using JOIN Statement
    7. UPDATE using JOIN Statement
    8. Custom JOINs 
    9. JOINs with Sub-Queries


    1. Introduction

    • Previously, JOIN-operations was performed in WHERE-clause, but new syntax introduce JOIN-statement as extention to FROM-clause, so that you can implement JOIN within FROM-Clause instead of WHERE-Clause.
      Note that still you can use previous syntax for JOIN
      that does not includes JOIN-statement.
    • Specifiying JOIN condition in FROM clause (as new syntax support) is recommended because:
      1. ANSI Standard: According to ANSI specification, joining condition must be in FROM clause.
      2. Readability: It increases readability; you can easily determine the kind by finding relevant keywords as INNER JOIN, OUTER JOIN, and CROSS JOIN. 
      3. Filter Isolations: It isolates the joining conditions from data filtering condition. So, you can specify joining condition in FROM clause and data filtering condition in WHERE clause.
    • For joining, the column types to be joined need not be same but values expect same.
        E.g., 10 = "10"  => true;
                10=10.00 => true
    •  JOINs not only work with SELECT-statement they also work with UPDATE and DELETE statements.

    2. Types of JOINs


    SQL Server joins fall into three types:
    1. INNER JOIN: It merges (or  combines) matched rows from two tables. The matching is done based on a common columns of tables and their values satisfying joining condition.
    2. OUTER JOIN: It returns both matched and unmatched rows between two tables. Matched rows are combined with each other and unmatched rows are combined with NULL-rows. i.e., rows satisfying joining conditions are combined with each other and those does not satisfying are combined with NULL-Rows. However, you can filter unmatched rows in result set by using sub-types: LEFT | RIGHT OUTER JOINs. The following are types of OUTER JOINS
      • LEFT Outer Join (a.k.a LEFT-JOIN): Returns matched rows from two tables and unmached from LEFT table (or first table) only.
      • RIGHT Outer Join (a.k.a RIGHT-JOIN):Returns matched rows from two tables and unmached from RIGHT table(or second table) only.
      • FULL Outer Join (a.k.a FULL-JOIN): Returns matched rows from two tables as well as unmatched rows also from two tables.
    3. CROSS JOIN: This join does not merges/combines instead it perform cartesian product. It is similar to simple SELECT without joining condition or without WHERE-cluase.
    Microsoft SQL Server Training Online Learning Classes Sql JOIN Types INNER-OUTER-CROSS

    The other categories of joins are Equi-join, Natural Join, Self Join etc these can be achieved using INNER JOIN , OUTER JOIN and CROSS Joins. Using CROSS JOIN, you can achieve Cartesian product.

    • INNER JOIN merge records M + N 
    • OUTER JOIN merge records M+N for match; (M-R) X (N-R) for unmatched. 
    • CROSS JOIN perform Cartesian product as M X N.


    3. INNER JOIN

    • INNER JOIN works on conditional matching and returns matched rows from multiple tables.
    • Using INNER JOIN you can achieve EQUI-JOIN, NATURAL JOIN, and SELF-JOIN operations.
      • Equi-join (based equality condition and result containing duplicate columns)
      • Natural-Join (based on equality condition and result doesn’t contain duplicate columns).
      • Self-Join (Joining a table to it self).
    • It is Default JOIN operation. i.e, you can use just JOIN keyword instead of INNER JOIN in the query.
    • Using CROSS JOIN with equality condition in WHERE clause, you can achieve INNER JOIN.

    Syntax:

    FROM Table_1 [AS] t1
      [INNER] JOIN Table_2 [AS] t2 ON Joining_Condition1
      [[INNER] JOIN Table_3 [AS] t3 ON Joining_Condition2]...

    Where Joining_condition1,2 ..: Simple(means without logical operators) or complex
                                                       conditions(using complex operators such as AND, OR,NOT),
                                                       i.e, you can assign filtering condition in FROM clause also.

    Examples:

    1. Default JOIN is INNER-JOIN

      SELECT *
      FROM Products p JOIN Suppliers S ON P.SupplierID = S.SupplierID
    2. Simple INNER JOIN (Joining two tables)

      SELECT *
      FROM Products p
         INNER JOIN Suppliers S ON P.SupplierID = S.SupplierID
    3. Complex INNER JOIN (Joining more than two tables with filtering)

      SELECT *
      FROM Products p
        INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID
        INNER JOIN Vendors V ON V.SupplierID = s.SupplierID
      WHERE p.ProductID < 4
    4. Achieving Equi-join using INNER JOIN. (select identical columns from both tables)

      SELECT t1.RNo, t1.sname,t1.Address, t2.Rno AS ex1, t2.Total
      FROM StudentInfo t1
        INNER JOIN StudentMarks t2 ON t1.Rno=t2.Rno
      =
      SELECT * FROM StudentInfo t1, StudentMarks t2
      WHERE t1.rno = t2.rno
    5. Achieving NATURAL JOIN using (Select only one identical column from any table)

      SELECT t1.Rno, t1.sname, t1.Address, t2.Total
      FROM StudentInfo t1
        INNER JOIN SudentMarks t2 on t1.Rno = t2.Rno
    6. Achieving SELF JOIN (FIND DUPLICATE RECORDS)

      SELECT * FROM StudentInfo AS s1
         INNER JOIN StudentInfo AS s2 
              ON s1.sname = s2.sname AND s1.Sid <> s2.Sid
    7. Achieving INNER JOIN using CROSS JOIN.

      -- using CROSS -- Equivalent to
      SELECT *
      FROM Table1 t1
      CROSS JOIN Table2 t2
      WHERE t1.ID =t2.ID
      SELECT *
      FROM Table1 t1
         INNER JOIN Table2 t2
      ON t1.ID = t2.ID


    4. OUTER JOIN

    • Unlike INNER JOIN, OUTER JOIN retrieves both matched and unmatched records.
    • OUTER JOIN can be LEFT, RIGHT or FULL outer Join.
    • LEFT OUTER JOIN (simply LEFT JOIN), returns matched records from both tables and unmatched record from first(left) table. Similarly, RIGHT OUTER JOIN(simply LEFT JOIN) returns matched and unmatched records from second(right table). FULL JOIN (Full Outer Join) returns matched and unmatched records from both the table.
    If no matching rows found, the associated result row contains null values for all select columns.

    Syntax:

    SELECT "SELECT list"
    FROM LeftTablel
       "LEFT|RIGHT|FULL" [OUTER] JOIN  RightTable ON "join condition"

    Examples:

    Microsoft SQL Server Training Online Learning Classes Sql JOIN Operations INNER OUTER CROSS

    1. LEFT OUTER JOIN (Result Set = Matching from both tables + un-matching from left table)

      SELECT *
      FROM T1
        LEFT OUTER JOIN T2 ON T1.col1 = T2.col1
    2. RIGHT OUTER JOIN(Result Set = Matching from both tables + un-matching from right table)

      SELECT *
      FROM T1
          RIGHT OUTER JOIN T2 ON T1.col1 = T2.col1
    3. FULL OUTER JOIN (Result Set = Matching and un-matching from both tables)

      SELECT *
      FROM T1
         FULL OUTER JOIN T2 ON T1.col1 = T2.col1
    4. Achieving Equi-join (By filtering using WHERE clause, you can achieve equi-join)

      SELECT *
      FROM T1
          FULL OUTER JOIN T2 ON T1.col1 = T2.col1 
      WHERE (T1.col1 IS NOT NULL) AND (T2.col1 IS NOT NULL)

      -- Equals to  --

      SELECT *
      FROM T1
         INNER JOIN T2 ON T1.col1 = T2.col1
    5. Achieving Self-join

      SELECT t1.col1, t1.col2, t2.col1 AS Expr1, t2.col2 AS Expr2
      FROM T1 AS t1
         FULL OUTER JOIN T1 AS t2 ON t1.col1 = t2.col1


    5. CROSS JOIN

    • CROSS JOIN returns all possible combinations of rows from both joined table.
    • Using CROSS JOIN without WHERE clause, you can achieve Cartesian product.
    • If Table A has 7 rows, and table B has 3 rows, then result set would have 7x3=21 rows

    Examples:

    1. Achieving CARTESIAN PRODUCT

      --Simple CROSS JOIN -- Equivalent example
      SELECT * FROM Table1
      CROSS JOIN Table2
      SELECT *
      FROM Table1,Table2
    2. Achieving Self-join using CROSS JOIN.

      SELECT * FROM Table1 CROSS JOIN Table1
    3.  Achieving INNER join using CROSS JOIN.

      -- using CROSS -- Equivalent to
      SELECT *
      FROM Table1 t1
      CROSS JOIN Table2 t2
      WHERE t1.ID =t2.ID
      SELECT *
      FROM Table1 t1 INNER JOIN Table2 t2
      ON t1.ID = t2.ID

      • Using OUTER JOIN you cannot achieve CROSS JOIN or vice versa. Similarly, using INNER JOIN you cannot achieve OUTER JOIN, CROSS JOIN.
      • Cross Join returns zero-rows resultset when either of the table is empty.

    6. DELETE using JOIN Statement

    1. DELETE+INNER Join - 1:
      Deleting all COMMON records from two tables

      DELETE Table1, Table2
      FROM Table1 t1
        INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
    2. DELETE+INNER Join - 2:
      Deleting COMMON records from left table only

      DELETE Table1
      FROM Table1 t1
        INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
    3. DELETE+INNER Join-3:
      Deleting COMMON records from RIGHT table only.

      DELETE Table2
      FROM Table1 t1
        INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
    4. DELETE+OUTER Join-1:
      Deleting UN-COMMON records from BOTH tables.

      DELETE Table1, Table2
          FROM Table1 t1
             FULL OUTER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
       WHERE t1.PrimaryKey IS NULL AND t2.ForiegnKey IS NULL;
    5. DELETE+OUTER Join-2:
      Deleting UN-COMMON records from LEFT table only.

      DELETE Table1
         FROM Table1 t1
             LEFT JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
       WHERE t1.PrimaryKey IS NULL;
    6. DELETE+INNER Join-3:
      Deleting COMMON records from RIGHT table only.

      DELETE Table2
         FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
      WHERE t2.ForiengKey IS NOT NULL;

    7. UPDATE using JOIN Statement

    1. UDPATE+JOIN -1:
      Updating all records of Left tables that matches(COMMON) with right table.

      UPDATE Table1
      SET Column1 = Value1, Column2=Value2....
      FROM Table1 t1
         JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;

      You can't update more that one table in a single statement.
    2. UPDATE+JOIN -2:
      Updating all records of right tables that matches(COMMON) with left table.

      UPDATE Table2
       Set Col1=Value1, Col2=Value2...
      FROM Table1 t1
          JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
    3. UPDATE+OUTER JOIN -1 :
      Updating all records of table1 those are UN-COMMON with table2.

      UPDATE Table1
         Set Col1=Value1, Col2=Value2...
      FROM Table1 t1
         FULL OUTER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
      WHERE t1.Primarykey IS NULL;

    8. CUSTOM JOINS

    Microsoft SQL Server Training Online Learning Classes Sql JOIN Operations INNER OUTER CROSS

    1. Negation of INNER JOIN:
      Find all records of two tables those are unmatched with each other.

      SELECT *
       FROM TableA a
          FULL OUTER JOIN TableB b ON a.PrimaryKey = b.ForiegnKey
      WHERE a.PrimaryKey IS NULL and b.ForeignKey IS NULL;
    2. Negation of LEFT OUTER JOIN:
      Find all records of a Table1 those does not match with other Table records:

      SELECT a.*
        FROM TableA a
          LEFT JOIN TableB b ON a.PriamryKey=b.ForeignKey
      WHERE a.PrimaryKey IS NULL;
    3. Negation of RIGHT OUTER JOIN:
      Find all records of RIGHT-table those does not matched with LEFT-table.


      SELECT b.*
        FROM TableA a
          RIGHT JOIN TableB b ON a.PriamryKey=b.ForeignKey
      WHERE b.ForeignKey IS NULL
    4. LEFT CROSS JOIN:
      Find all records joined with 1st record of the table;


      SELECT *
        FROM TableA as A CROSS JOIN (Select Top 1  *  From Table B) as B;

    5. Negation of RIGHT OUTER JOIN:
      Find all records of RIGHT-table those does not matched with LEFT-table.


      SELECT *
        FROM (Select Top 1 * from TableA ) a
          CROSS JOIN TableB b


    9. JOINs with Sub-Queries

    T-Sql allows you to nest the sub-queries within JOIN Clause.
    1. Sub-Query with INNER JOIN

      SELECT *
      FROM (Select * from Table1 ......) as t1
         INNER JOIN (Select * from Table2 .....) as t2  ON t1.Id = t2.Id;
    2. Sub-Query with OUTER JOIN

      Select *
      FROM Table1 as t1
       FULL OUTER JOIN (Select * from Table2 Where ......) as t2 ON t1.Id=t2.Id