More on SQL indexes

the command to show index statistics:

dbcc show_statistics (tablename, indexname)

The lower the "All density" value the better, means the index is highly selective.

--
How to view index fragmentation (replace database and table names)

   1:  select str(s.index_id,3,0) as indid,

   2:  left(i.name, 20) as index_name,

   3:  left(index_type_desc, 20) as index_type_desc,

   4:  index_depth as idx_depth,

   5:  index_level as level,

   6:  str(avg_fragmentation_in_percent, 5,2) as avg_frgmnt_pct,

   7:  str(page_count, 10,0) as pg_cnt

   8:  FROM sys.dm_db_index_physical_stats

   9:  (db_id('_DBNAME_<dbname>'), object_id('<tablename>_TABLENAME_'),1, 0, 'DETAILED') s

  10:  join sys.indexes i on s.object_id = i.object_id and s.index_id = i.index_id

How to reorganise an index (online operation) - only affects the leaf nodes.

   1:   

   2:  ALTER INDEX <indexname> _INDEXNAME_ on _TABLENAME_ <tablename> REORGANIZE

To rebuild the index without taking the database offline you need the enterprise edition of MSSQL 2005.

   1:  ALTER INDEX  _INDEXNAME_ on _TABLENAME_  REBUILD

If space is an issue it is better to disable a nonclustered index before rebuilding it, then we only need about 20% of the index size as free space to do the rebuild.

Also the Fill Factor can be adjusted based on how we expect the data to be used. This specifies how full the data pages or leaf level index pages will be. Lower fill factor is better when there are many data updates and inserts.