Na view sys.dm_db_index_physical_stats há uma coluna chamada index_type_desc. Essa coluna indica a descrição do tipo do índice.
As possibilidades de valores nessa coluna se resumem as descritas abaixo:
Clustered Index: esse tipo de índice ordena fisicamente os dados da tabela, usado em colunas que tenham baixa seletividade e/ou, seja frequentemente pesquisada por um range de dados. Só pode haver um clustered index por tabela e esse pode conter no máximo 16 colunas e o tamanho máximo criado para o index key é de 900 bytes.
Non-Clustered Index: esse tipo de índice não impõe ordenação por este motivo é possível existir múltiplos índices deste tipo em uma coluna, contudo há uma limitação de 1000 non-clustered index por tabela. O Non-Clustered Index cria uma estrutura separada que aponta para a localização das páginas de dados. Usado em colunas que são pesquisadas por um valor (não por um range de valores) e em colunas de alta seletividade.
XML Indexes: são índices criados para facilitar a busca de dados dentro de colunas do tipo XML. Os XML Indexes são divididos em dois tipos:
- Primary: é criado para todos os nós da coluna XML;
- Secondary: são índices criados para um Path ou Node da coluna XML.
Spatial Index: esse tipo de índice é utilizado em colunas do tipo geometry ou geography.
Heap: se os dados de uma tabela não estão logicamente classificados, ou não há ordem de dados especificados na tabela isso será considerado um Heap Structure.
Para maiores informações sobre a sys.dm_db_index_physical_stats basta acessar o link http://msdn.microsoft.com/pt-br/library/ms188917.aspx
Abraço, Rodrigo