Indexes

Features

1) Improves performance

2) Could be built on one or more columns in a table or view also

these are automatically created for primary keys and unique

constraints

We have two types of indexes

1) Clustered Index.

2) Non-Clustered Index

Clustered Index

Leaf Node contain actual data pages, data is sorted and stored based on

index key.

Clustered index are normally designed for a table which has a query which

returns larger amount of data for example queries having where data between

certain range

CREATE CLUSTERED INDEX <INDEX_NAME> ON dbo.<table_name>

(

)

Nonclustered Index.

Leaf nod contains the pointer to the actual data pages.

Non-Clustered index are normally designed for a table which have query

that returns small matches for example for queries having where="value"

CREATE NONCLUSTERED INDEX <INDEX_NAME> ON dbo.<table_name>

(

<column_name> asc

)

Maintaining our indexes.

Rebuilding and re-organizing

Rebuild : Drops the index and recreated the index

Reorganize : Just re-organizes the leaf nodes

You should use re-org when fragmentation is less than 30%, else rebuild.

In enterprise version we have option of online index rebuilding i.e index is

accessible during the rebuilding process.

ALTER INDEX <INDEX_NAME> ON dbo.<TABLE_NAME> REORGANIZE

ALTER INDEX <INDEX_NAME> ON dbo.<TABLE_NAME>

REBUILD WITH (ONLINE=ON)

To see the fragmentation of the indexes.

select * from sys.dm_db_index_physical_stats(db_id(<database_name),null,null,null,null) as stats
join sys.indexes as ion stats.object_id=i.object_id and stats.index_id=i.index_id

select (SELECT NAME FROM SYS.TABLES WHERE OBJECT_ID=I.OBJECT_ID) TABLE1, * from sys.dm_db_index_physical_stats(db_id('WF'),null,null,null,null) as stats
join sys.indexes as ion stats.object_id=i.object_id and stats.index_id=i.index_id

Drop unutilized indexes

select * from sys.dm_db_index_usage_stats

we will do join on indexes

select object_name(u.object_id) as object_name, *from sys.dm_db_index_usage_stats as Ujoin sys.indexes as Ion u.object_id = i.object_id and u.index_id=i.index_id

Page updated

Google Sites

Report abuse