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>
(
ID
)
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 statsjoin sys.indexes as ion stats.object_id=i.object_id and stats.index_id=i.index_id
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