Clustered Index

Table of contents

Types of indexes………………………………………………………………………………………………2

Clustered Index……………………………………………………………………………………2

Non-clustered Index…………………………………………………………………………2

Selection of indexes by Optimizer…………………………………………………2

Search Arguments…………………………………………………………………………………2

Index Selection……………………………………………………………………………………3

Index Distribution Stats……………………………………………………………3

Index Density…………………………………………………………………………………………4

Index Covering………………………………………………………………………………………4

Optimization of Cursors……………………………………………………………………………5

Optimization of temp tables…………………………………………………………………6

Some general rules for optimization……………………………………………7

Some useful commands……………………………………………………………………………………10

Types of indexes

Clustered Index (CI)

The data in this case is physically stored in the order of the index. The leaf level of the index is the same as the data pages. There can be only one CI on a table as the data can physically be sorted in only one order. The select is extremely efficient with CI. The CI is extremely efficient in the following cases:

a) where fname like "Ram%"

b) where author_id between 1 and 7

c) where Price > 345.34

d) group by author_id

e) order by author_name

Non-clustered index (NI)

The data in this case is not stored in the order an

index is stored. The leaf-level of the index contains the various index keys and a pointer to the row as rowID (page no. + row offset). There can be 249 NI on a table. The NI should be used when

a) The number of rows returned is small.

b) When where clause limits the number of rows (usually

'=' operator)

c) When the query can be covered.

Selection of indexes by Optimizer

SEARCH ARGUMENTS (SARG)

These are the expressions on the RHS of the where clause. They act as a kind of (dis)incentive to the optimizer to use the index on the column. Some search arguments are:

where author_id = '13'

where fname like "Ram%"

where Price > 2347.32

Some expressions that are not valid SARGs are:

Invalid Valid

Price*1.5 = 1000 Price = 1000/1.5

Qty + 10 = 200 Qty = 200 - 10

fname + '' + lname fname = 'John'

= "John Gray" and lname = 'Gray'

Substring(1,3,fname) = 'KIR' Name like "KIR%"

isnull(lname,"N") = "N" lname is null

The index might not be used in the case of following SARGs:

1) No start point for the index.