can not delete index

[abraka1]

The index present in sysindexs with key = NULL. Can not delete same as same is not displayed on table properties/indexes. How to delete same from sysindexes ?

 

[JamesLean]

It sounds like you are talking about the "heap" of a table without a clustered index. This will have an indid of 0 and cannot be deleted.

The reason it doesn't show on the table properties within Enterprise Manager is that it is not really an "index" but is the collection of actual data pages that make up the table.

If a clustered index is defined on a table you will have an entry in sysindexes with indid of 1 and will not have one with indid of 0 as the data is physically stored in the order of the index.

Check BOL for more info about table and index architecture. --James

 

[tlbroadbent]

There can actually be 2 entries in the sysindexes table with a null keys column. As James explained, one is for INDID=0 row which occurs on tables with no clustered index. The other is INDID=255 which indicates the table contains TEXT or IMAGE type data.

You should not attempt to delete either row because they are essential to proper function of SQL Server. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm