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