Creating and Managing Indexes in SQL Server

Discussion in 'SQL Server' started by MinalS, Jan 13, 2015.

  1. User needs to create and manage the indexes in SQL Server. There are various types of indexes in SQL server. We shall explore in detail about the indexes in SQL Server.

    An index is a structure associated with the SQL Server for implementing the indexes on the tables. It enables faster data searching. There are several advantages provided by the indexes. User can accelerate the queries for joining the tables and provide sorting and grouping of data. The uniqueness of rows is enforced on every table. The index contains keys and pointers. The keys are created using one or more columns in the table. The pointers are used for storing the address of the location where the data page is placed in the memory.

    The users query for the data to be retrieved from the tables. The data is searched based on the key column values. The processor scans for the data, accesses the information and user can use the information. It reduces the processing time as it does not search for the data in the data pages.

    The following indexes types are provided by the SQL Server.
    1. Clustered Index
    2. Nonclustered Index
    1. Clustered Index

    A clustered index is used for sorting and storing the data rows in the table based on the key values. The data is stored physically when clustered index is defined on the table. As only one clustered index can be defined on the table, user must define it on the attributes with high percentage of unique values.

    2. Nonclustered Index

    The nonclustered index contains the key values and the row locators to point to the storage location of the data in the table. The physical order of the rows is not similar to the index order. The indexes are created on the columns used in joins. The CREATE INDEX command is used for creating the nonclustered index in SQL Server.

    The data in the nonclustered index is placed in the random order. The logical ordering is specified by the index. The data rows are available throughout the table. The nonclustered index contains the index keys in a sorted order; the leaf of the index contains the pointer to the data page.

    Creating Indexes



    The index must be created on the columns that are queried most by the users. But sometimes there are requirements when user needs to create the index on more than one column. The index based on more than one column is known as composite index.

    The CREATE INDEX statement is used for creating an index in SQL Server. The syntax for creating the index is as shown below:

    Code:
    
    CREATE [ UNIQUE] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ { database_name. [ schema_name ] . | schema_name. } ]
    { table_or_view_name } ( column [ ASC | DESC ] [ ,… n ] )
    [ INCLUDE ( column_name [ ,….n ] ) ]
    [ WITH ( <relational_index_option>[, …n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
    | filegroup_name | DEFAULT } ]
    < relation_index_option>::= 
    { PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NO_RECOMPUTE = { ON | OFF }
    | DROP _EXISITNG = { ON | OFF }
    | ONLINE = { ON | OFF }
    
    
    Where,
    • UNIQUE creates an index and every row has a different index value.
    • CLUSTERED specifies the clustered index where the data is stored on the index
    • NONCLUSTERED is used to organize the data logically.
    • index_name is the name of the index
    • table_or_view_name is the name of the table or the view that contains the attribute
    • column is the column name on which the index is specifed
    • INCLUDE ( column_name [ ,…n ] ) ] is the column of the non clustered index
    • ON partition_scheme_name ( column_name ) is the partition scheme along with the column name
    • ON filegroup_name is the filegroup on which the index is created
    • ON DEFAULT is the specified index created on the default filegroup
    • PAD_INDEX = { ON | OFF } is the index padding
    • FILLFACTOR is the percentage indicating the full level of the leaf node on the index page
    • IGNORE_DUP_KEY = { ON | OFF } is used for indicating a duplicate key
    • STATISTICS_NO_RECOMPUTE = { ON | OFF } is used to specify the recomputing of the distribution
    • DROP_EXISTING = { ON | OFF } is used to state the preexisting clustered, nonclustered or XML index dropped
    • ONLINE = { ON | OFF } is used to check the tables and indexes for querying the data in the index operation
    Consider an example of creating a clustered index on the StudID attribute of the Student table. The following code is used for creating the index.

    Code:
    CREATE CLUSTERED INDEX IX_StudID ON Student ( StudID ) WITH 
    FILLFACTOR = 10
    
    In the above code, the FILLFACTOR value 10 is mentioned. The free space of the page is reserved.

    The nonclustered index is created on the EmpID of the Employee table. The code to demonstrate the use of the nonclustered index is as shown below:

    Code:
    CREATE NONCLUSTERED INDEX IDX_EmpID ON Employee ( EmpID )
    

    Filtering Indexes



    User has created an index on a column of the specified table. If there is large data, the indexing requires more processing time. The filtering index is used to overcome this problem. The filter index is created on the specific column. It created a filter to index a subset of rows in the table. The storage space and maintenance cost is reduced in filtered index.

    User can create only non clustered filtered index on the table. Consider an example, a filtered index is created in the Student table, for the name column where the value is Sam. The following statement is used for creating the filtered index.

    Code:
    CREATE NONCLUSTERED INDEX FX_StudID ON Student ( StudID ) WHERE name = ‘Sam’
    
    In the above code, the FX_StudID index is created on the StudID column of the Student table based on the name value.

    Creating XML Indexes

    If user has a data in column is in the XML format, the processor parses the XML data every time the query is executed. The XML values are large in size and the processor takes time to execute them. If user wants to increase the data processing speed, user can create the index based on the XML data values. It is known as XML indexes.

    There are two types of XML Indexes. They are as follows
    1. Primary XML Index
    2. Secondary XML Index
    1. Primary XML Index

    It is a clustered B – Tree representation of the XML data. Once the user creates an index on a column with the XML data type, a notification will be added to the nodes in the XML data. For creating the primary XML index, the table must contain a clustered index on the primary key column.

    The CREATE PRIMARY XML INDEX and CREATE XML INDEX commands are used for creating XML indexes. The following code is used for creating the primary index on the XML column.

    Code:
    CREATE PRIMARY XML INDEX PXML_OrderDetails_OrderID ON 
    OrderDetails ( ProductName )
    
    In the above code, the index is created on the nodes in the XML data stored in the ProductName column.

    2. Secondary XML Index

    There must be a primary index before user creates the secondary index. Once user creates the primary index, there are three types of secondary index created on the table. The secondary indexes are useful in the processing of the XQuery.

    There are three types of secondary XML indexes.
    1. Path indexes
    2. Value indexes
    3. Property indexes
    1. Path indexes

    The path index is created by using the path id and the value column. It helps user improve the performance of queries using the path and values on the selected data.

    Consider an example to execute the query for the presence of the Order ID in the table using the XQuery expression. User can create the path secondary index on the ProductName column of the OrderDetails table.

    The following code is used to represent the Path index on the ProductName column.

    Code:
    CREATE XML INDEX PIdx_OrderDetails_PATH ON OrderDetails 
    ( ProductName ) USING XML INDEX PXML_OrderDetails_ProductName 
    FOR PATH
    
    The above code creates the path index on the ProductName column.

    2. Value Indexes

    The value indexes have the items similar to the path indexes. The only difference is they appear in reverse order. The value of the column appears before the path id. The value secondary index can be created on the primary index created previously.

    The following code is used to represent the value index created on the ProductName column.

    Code:
    CREATE XML INDEX PIdx_OrderDetails_VALUE ON OrderDetails
    ( ProductName ) USING XML PXML_OrderDetails_ProductName FOR VALUE
    
    In the above code, a value index is created on the ProductName column.

    3. Property Indexes

    The property index contains the primary key created on the table, path id, and the columns of the primary XML indexes. The performance of the queries used as paths for selecting data is improved.

    The following code is used to create the property index on the ProductName column.

    Code:
    CREATE XML INDEX PIdx_OrderDetails_PROERTY ON OrderDetails
    ( ProductName ) USING XML INDEX PXML_OrderDetails_ProductName 
    FOR PROPERTY
    
    In the above code, the property index is created on the ProductName column.

    Creating Partitioned Indexes



    The indexes can be partitioned in the SQL Server depending on the range values. The partitioned indexes are useful in improving the performance of the query. User can easily access the data from the tables. If the datasets are large in size it can affect the performance of the processor.

    To overcome this issue, the partitioned indexes are used as the data is divided into smaller groups. In partitioning the data is distributed into several filegroups. The data is efficiently managed in the database.

    The following tasks are performed while creating the partitioned index.
    1. Create the partition function
    2. Create the partition scheme
    3. Create the clustered index
    1. Create the partition function

    User can create the partition function for creating the partition index. The boundary value of the partitions is defined in the partition function.

    Consider the example of the partition function checking the results of the students every year. The Student table containing result for the last five years beginning from 2010 is partitioned. The partition function for the table is as shown below:

    Code:
    CREATE PARTITION FUNCTION PFResultDate ( datetime ) AS 
    RANGE RIGHT FOR VALUES ( ‘2011 – 01-01’, ‘2012-01-01’, ‘2013-01-01’, 
    ‘2014-01-01’ )
    
    In the above statements, the partition function is created. It defines the four boundary values. It contains five partitions. The first boundary partition contains value less than the 2011-01-01. The second partition contains value greater than or equal to 2012-01-01 or less than or equal to 2013-01-01 and so on.

    2. Creating Partition Scheme

    Once the partition function is created, the partition scheme is connected to the partition function. The data of every partition is stored in a filegroup. The filegroup and the partition numbers must be the same.

    The following example is used to demonstrate the creation of a filegroup on the function.

    Code:
    CREATE PARTITION SCHEME PSResultDate AS PARTITION 
    PFResultDate TO ( fg1, fg2, fg3, fg4, fg5 )
    
    In the above code, the partition scheme PSResultDate is created.

    3. Creating Clustered Index

    Once the partition scheme is created, the clustered index is created. The clustered index is always created on the column containing unique values in the table. The clustered index is associated with the partition scheme as shown below:

    Code:
    CREATE CLUSTERED INDEX Ix_StudID ON Student ( StudID ) 
    ON PSResultDate ( ResultDate )
    
    The above code will divide the table data into five filegroups for the values stored in the ResultDate column.

    Managing Indexes



    User can maintain the indexes in the SQL Server. The index maintenance contains tasks like enabling, disabling, renaming and dropping the indexes.

    1. Disabling Indexes

    Once the index is disabled, user cannot access it. The data is present in the table. The Data Modification Language ( DML ) operations cannot be performed on the disabled index. For enabling and rebuilding the index, the ALTER INDEX REBUILD statement or CREATE INDEX WITH DROP_EXISTING statement commands are used.

    The following statement is used for disabling the index.

    Code:
    ALTER INDEX IX_StudID ON Student DISABLE
    
    2. Enabling Indexes

    Once user has disabled the index, it is in the state until user rebuilds or drops the index. The disabled index can be enabled using any of the following methods.
    1. The ALTER INDEX statement along with the REBUILD clause
    2. The CREATE INDEX statement along with the DROP_EXISTING clause.
    Any one of the above statements can be used for rebuilding the index status when set to enable. Use can rebuild the disabled clustered index by setting the ONLINE option ON. The DROP_EXISTING clause is used for rebuilding the index after dropping the existing index. The default value of the clause is set to OFF.

    3. Renaming Indexes

    User can rename the current index using the sp_rename system stored procedure. The following statement is used for renaming the index.

    Code:
    EXEC sp_rename ‘Student.IX_Data_StudID’ ‘IX_StudID’, ‘index’
    
    In the above statement, the IX_Data_StudID’ is renamed to IX_StudID.

    4. Dropping Indexes

    User can remove the unwanted indexes from the database. The DROP command is used for dropping indexes.

    The following statement is used for dropping the index in SQL Server.

    Code:
    DROP INDEX IDX_Product_ProductID ON Product
    
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice