Index Discussion

Hi Folks,

Got a topic open for debate.

We currently have an archive table - DDL

CREATE TABLE [dbo].[Audit] (
[id] [int] identity (1,1) NOT NULL ,
[col1] [char] (10) NOT NULL ,
[col2] [char] (15) NOT NULL ,
[col3] [int] NOT NULL ,
[col4] [varchar] (50) NOT NULL ,
[col5] [datetime] NOT NULL ,
[col6] [varchar] (4000) NULL ,
[col7] [char] (3) NULL
)
GO


This table grows to about 40 million rows during the course of the month. The table has a clustered index on the id field and a non clustered index on the col2 and col3. The id column is not used in queries. At the moment we run weekly dbcc reindexes on all the indexes. We are running into a space issue on the reindex of the clustered index (copying the whole table out , ordering etc) and are considering dropping the index or changing to a non clustered index. (The DBCC utility that we have built will only rebuilt all the indexes or none at all.)

I feel this is not a good idea and know my reasons. I would like some input as to why this might prove a bad idea.

Will it increase page splitting? Will the table performance be impacted even if the queries are not specifically using the clustered index?

What are the reasons for and against?

Thanks Folks