Objectives

1

• Objectives
• In this lesson, you will learn to
• Create a clustered index
• Create a nonclustered index
• Use the Index Tuning Wizard
• Understand Index Enhancements

2

• Getting Started
• An index is an internal table structure that SQL
  Server uses to provide quick access to rows of a
  table based on the values of one or more columns
• Advantages of Using Indexes
• Improve the speed of the execution of queries
• Enforce uniqueness of data i.e. Indexes can
  enforce entity integrity.
• Indexes can improve the performance of queries
  that use joins to retrieve tables.

3

• Getting Started (Contd.)
• Disadvantages of Using Indexes
• Takes time to create an index
• Takes large amount of disk space to store data
  along with the original data sourcethe table
• Gets updated each time the data is modified
• Types of Indexes
• Clustered index
• Nonclustered index

4

• Getting Started (Contd.)
• Clustered Index
• In a clustered index
• The data is physically sorted
• Only one clustered index can be created per table
• Nonclustered Index
• In a nonclustered index
• The physical order of the rows is not the same as
  the index order

5

• Getting Started (Contd.)
• Nonclustered indexes are typically created on
  columns used in joins and WHERE clauses, and
  whose values may be modified frequently
• SQL Server creates nonclustered indexes by
  default when the CREATE INDEX command is given
• There can be as many as 249 nonclustered indexes
  per table

6

• Getting Started (Contd.)
• Indexes and Heap Structures
• SQL Server supports indexes defined on any column
  in a table, including computed columns
• If a table does not have any clustered index,
  data is not stored in a particular order. This
  structure is called a heap

7

• Getting Started (Contd.)
• Features of Indexes
• Indexes accelerate queries that join tables, and
  perform sorting and grouping.
• Indexes can be used to enforce uniqueness of
  rows.
• Indexes are useful on columns in which the
  majority of data is unique.
• When you modify the data of an indexed column,
  the associated indexes are updated automatically.
• You require time and resources to maintain
  indexes. You should not create an index that is
  not used frequently.

8

• Getting Started (Contd.)
• A clustered index should be created before a
  nonclustered index. A clustered index changes the
  order of rows. A nonclustered index would need to
  be rebuilt if it is built before a clustered
  index
• Typically, nonclustered indexes are created on
  foreign keys.
• Syntax
• CREATE UNIQUE CLUSTEREDNONCLUSTERED INDEX
  index_name
• ON table_name(column_name,column_name)

9

• Optimizing Query Execution
• The Employee table contains a large amount of
  data. The first name of each employee and the
  name of the Department are required to create a
  report. However, it takes a long time to execute
  the following query.
• SELECT vFirstName, cDeptName
• FROM Employee JOIN Department
• ON
• Employee.cDeptno Department.cDeptno
• Suggest and implement a solution for faster
  data retrieval.

10

• Task List
• Identify how to speed up data retrieval
• Draft the statement to create an index
• Create the index in the database
• Verify that the index has been created
• Verify that the query execution is faster

11

• Identify how to speed up data retrieval
• Indexes are used to
• Speed up data retrieval
• Enforce the uniqueness of rows
• Result
• To speed up data retrieval, use indexes

12

• Draft the statement to create an index
• Action
• The tables on which the index would be created
  are Department and Employee
• The attributes on which the index would be
  created are cDeptNo of Employee and cDeptNo of
  Department
• The types of indexes to be created are
• Employee - Nonclustered index
• Department - Clustered index

13

• Create the index in the database
• Action
• In the Query Analyzer window, type
• CREATE NONCLUSTERED INDEX idxdno
• ON Employee(cDeptNo)
• CREATE CLUSTERED INDEX idxDeptDno
• ON Department(cDeptno)
• Press F5 to execute the code

14

• Verify that the index has been created
• To verify that the index has been created, use
  the sp_helpindex command
• Syntax
• sp_helpindex table_name
• Action
• In the Query Analyzer window, type
• sp_helpindex Employee
• sp_helpindex Department

15

• Verify that the query execution is faster
• Action
• Execute the query after creating the index. If
  there is a lot of data, you can note the
  difference in speed

16

• Wait a while
• How many clustered indexes can be created per
  table?
• Which index organizes data logically but does not
  store data physically?

17

• Composite Indexes
• It is simply an index that has more than one
  column in its key.
• Helpful wit queries that search for rows based on
  all of the values in multiple columns, such as
  LastName FirstName.
• Up to 16 columns can be used in an index. The
  combined size of the columns cannot exceed 900
  bytes.
• SQL Server doesnt use the composite index unless
  the first column in the key is specified.
• When building a composite index, use the column
  with the most unique values as the first column.

18

• Unique Indexes
• A unique index will ensure that the values must
  be unique within the table.
• Unique indexes are how SQL Server enforces
  PRIMARY KEY UNIQUE constraints for entity
  integrity.

19

• Index Tuning Wizard
• Index Tuning Wizard available in SQL Server is
  used to select and create the best possible set
  of indexes and information regarding a database
• Uses of the Index Tuning Wizard
• For a given workload, the best possible
  combination of indexes for a database is
  recommended
• The effects of the proposed recommendation about
  the indexes, distribution of queries among
  tables, and the query performance in the workload
  will be analyzed
• For a small set of problem queries, the way to
  tune the database will be recommended
• It will specify the advanced options such as disk
  space constraints that can be customized

20

• Index Enhancements
• Fill Factor
• FILLFACTOR clause improves performance of the
  system by minimizing the amount of page splitting
  that occurs each time an index page becomes full
• Syntax
• CREATE CLUSTERED INDEX index_name
• ON table_name (column_name)
• WITH FILLFACTOR percentage_fillfactor
• Pad_Index
• Specifies the space to leave open on each page
  (node) in the intermediate levels of the index

21

• Index Enhancements (Contd.)
• Implications of NULL in Unique Indexes
• In a table, a unique index cannot be created on a
  single column if that column contains NULL in
  more than one row
• DBCC SHOWCONTIG
• The DBCC SHOWCONTIG command is primarily used to
  find out why the table or the index is heavily
  fragmented
• Syntax
• DBCC SHOWCONTIG (table_id , index_id)

22

• Index Enhancements (Contd.)
• The DBCC INDEXDEFRAG
• The DBCC INDEXDEFRAG command is used to
  defragment clustered and secondary indexes of the
  specified table or view
• Syntax
• DBCC INDEXDEFRAG

23

• Wait a while
• Neha wants to minimize the amount of page
  splitting that occurs each time an index page is
  full. What should she use?

24

• Performance Considerations (Contd.)
• Index Usage Criteria
• SQL Server cannot use an index until and unless
  the query contains a column in a valid search
  argument or join clause that matches at least the
  first column of the index

25

• Choosing columns for Index
• Primary keys and foreign keys should always be
  indexed.
• Create indexes on columns that you often search
  on.
• Create indexes on columns that are often used to
  sort the results of a query.
• Create unique indexes on a primary key or
  alternate key.
• If you often retrieve data in sorted order by a
  particular column, consider putting a clustered
  index on that column.
• Consider using a non-clustered index on the
  primary key of a table if the table uses an
  IDENTITY column as its primary key.

26

• Summary
• In this lesson, you learned that
• Indexes are created to enhance the performance of
  queries.
• There are two types of indexes clustered and
  nonclustered.
• Indexes are created using the CREATE INDEX
  statement.
• Data is physically sorted in a clustered index.
• Clustered indexes should be built on an attribute
  whose values are unique and do not change often.
• In a nonclustered index, the physical order of
  rows is not the same as that of the index order.

27

• Summary (Contd.)
• A nonclustered index should be built on an
  attribute which is normally used in joins and the
  WHERE clause. The values of this attribute may
  often change.
• A nonclustered index is the default index that is
  created with the CREATE INDEX command.
• The Index Tuning Wizard can be used to analyze
  the optimal use of indexes in the query entered
  in the Query Analyzer window.
• SQL Server provides the FILLFACTOR clause to
  improve performance of the system by minimizing
  the amount of page splitting that occurs each
  time an index page becomes full.

28

• Summary (Contd.)
• The DBCC SHOWCONTIG command is mainly used to
  find out whether the table or index is heavily
  fragmented. Table fragmentation normally occurs
  when a large number of insert and update
  operations are performed on the table.
• The DBCC INDEXDEFRAG command is used to
  defragment clustered and secondary indexes of the
  specified table or view.