8.1 : Indexes

SQL Server 2005

2

• Index• Need of an index• Types of indexes• To Work with an index• To Create indexes• To Drop indexes

Contents

3

Objectives

At the end of this presentation you should be able to:• Understand the concept of indexes• Understand why an index is required in a database• Understand the different types of indexes• Working with an index• Creating and dropping indexes

4

What is Index

• Indexes are used to speed up data retrieval• Indexes also enforce uniqueness of rows.• An index is an internal table structure that SQL

server 2005 uses to provide quick access to rows in a table based on the values of one or more columns.

5

Need of Indexes

• It Improves the speed of execution of queries.

• It speeds joins between tables

• They accelerate queries that join tables, and perform sorting and grouping.

• It can be used to enforce uniqueness of rows

• They are useful on columns in which the majority of data is unique. An index on columns containing large amount of duplicate data is not useful.

• When user modifies the data of an indexed column, the associated indexes are updated automatically.

6

Types of indexes

• Indexes can be classified as – Clustered Index– Non Clustered Index

• Clustered Index : The data is physically sorted. Only one clustered index can be created per table. So user should build it on attributes that have a high percentage of unique values and whose values do not change often.

7

Types of Index (Continued)

• Non Clustered Index : The physical order of the rows is not the same as that of the index order.

• Non clustered indexes have to be built on attributes which are used in joins and the WHERE clause.

• These are values which may change often• SQL server 2005 creates non clustered indexes by

default when the CREATE INDEX command is given

• There can be as many as 249 non clustered indexes per table .

8

To Work with Index

• Creating Index• Dropping Index• TO Create Index: User can create an index using

the create index statement

CREATE [UNIQUE] [CLUSTERED/NONCLUSTERED]INDEX index_name ON table_name (column_name [, column_name]…)

9

To Drop Index

• It removes one or more indexes from the current database.

• The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints (created by using the PRIMARY KEY or UNIQUE options of either the CREATE TABLE or ALTER TABLE statements, respectively).

Syntax

DROP INDEX 'table.index | view.index' [ ,...n ]

10

Example 1 - Index

• User can create the Index using Create Index statement

Create index pk1_Index on employees (EmployeeID)

(creates the index PK1_index on employees table)• User can view the index using statements sp_help Employees

sp_helpindex Employees

• User can drop the index using command drop index employees.pk1_index

11

Example 2 Index

CREATE TABLE publishers1( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind1

PRIMARY KEY CLUSTEREDCHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-

9]'), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT('USA'))

12

Key Points

• An index is an internal table structure that SQL server uses to provide quick access to rows. • Improves the speed of execution of queries.• When users modify the data of an indexed

column the associated indexes are updated automatically.

• Indexes can be classified as:– Clustered Index– Non Clustered Index

13

Key Points (Continued)

• In clustered Index, the data is physically sorted. Only one clustered index can be created per table.

• The non-clustered indexes do not physically order the data on the data pages.

• Drop Index statement is used for removing one or more indexes from the current database.

14

Activity Time (30 minutes)

• Activity: 8.1Create a unique clustered index on the Publisher_ID of the Publisher table. And list the index created.

• Activity: 8.2Create a nonclustered index on pub_name.

• Activity: 8.3Drop the index created in the Activity 1 and check to see whether the index is dropped.

15

Activity Time (30 minutes) continued

• Activity: 8.4Create and rebuild the index created in the Activity 1 to add another column Publisher_Name without dropping the index. (Which is called as a composite index; the index created on 2 or more columns).

Hint: to rebuild an index without dropping, use WITH DROP_EXISTING keyword used with the Create INDEX statement.

16

Questions & Comments