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

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

   • Class-1 : Creating, Finding, Droping, and Renaming Tables
   • Class-2 : Altering Tables and Adding Identity Column To Table

2. Lesson-2: Learning Integrity Constraints

   • Class-1: Overview of Integrity Constraints
   • Class-2: Primary Key and Unique Key Constraint
   • Class-3: Default Value  and Not Null Constraints
   • Class-4: Foreign Key Constraints with ON-UPDATE and DELETE options
   • Class-5: CHECK Constraints (custom business requirement)

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

   • Class-1: Basic INSERT | DELETES | UPDATES | TRUNCATES
   • Class-2: Basic SELECTs

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

   • Class-1: Advanced-DML with Top(n),CASE Clause, OUPUT-Clause

5. Lesson-5: Learning Advance SELECT

   • Class-1 Part-1: CTE- Common Table Expression
   • Class-1: Part-2: Pivot / Unpivot Operators
   • Class-2: Advance Grouping: COMPUTE,CUBE|ROLLUP,GROUPING,NTILE and PARTIION

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

   • Class-1: All-Types of Joins with multiple examples

7. Lesson-7: Learning Sub-Queries

   • Class-1: Sub-Queries : Co-related and Nested Sub-Queries

8. Lesson-8: Learning Temp-Tables:

   • Class-1 Local & Global Temporary Tables"

9. Lesson-9: Learning INDEXes

   • Class-1: Overview of INDEXes and their optimizations types etc.
   • Class-2: HowDoI CREATION,ALTER, DELETION and OPTIMIZATION etc"

10. Lesson-10: Learning VIEWS

    • Class-1: Overview and Operations of VIEWS

11. Lesson-11: Learning Built-in functions

    • Class-1: Introduction and examples of Scalar and Aggregate functions
    • Class-2: String functions
    • Class-3: TypeConversiions & Null Checking functions
    • Class-4: Date Functions
    • Class-5: Mathematical functions
    • Class-6:Ranking fucntions

12. Lesson-12: Learning Database Operations

    • Class-1: Creating Databases, Altering Databases, Using, finding and droping
    • Class-2: Attaching & DeAttaching Databases
    • Class-3: BackUP and Resoting Databases

        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

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

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

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: A^mxn to A^T=A^nxm

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 :

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:
   
   
   
It is similar to SELECT SUM(salary), AVG(salary). But not followed by Query res

2. 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

3. 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

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]
   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.

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.