Why am I seeing entries in sysindexes (or sp_helpindex) for indexes I have not created?

A. A. sysindexes contains rows per object as follows :-

. Every table has a row with an indid value of either 0 (heap) or 1 (if it has a clustered index).

. Every nc index has a row with indid > 1. 

. If text/image columns are used then these use rows.

.(SQL 7 only). There are rows for keeping track of non-index statistics on columns that SQL decides to capture statistics on even though there is no physical index on the column. Their names are _WA_Sys_<column>_<number>. These "indexes" cannot be dropped with a drop index command. They are there for the SQL optimiser to best choose query plans but if you want to drop them they can be with the drop statistics command.

. (SQL 7 only). When the index tuning wizard runs it creates "hypothetical" indexes with names like hind_<type>_<column>_<number>. These are removed when you create a "real" index on those columns using the script that the wizard produces for you.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.