kevaburg (4/26/2015)

---

Hi Folks,

This is my problem.

We have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.

Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.

This is the DDL for the table:

CREATE TABLE [dbo].[Inventory](

[EAN] [bigint] NOT NULL,

[Day] [smalldatetime] NOT NULL,

[State] [int] NOT NULL,

[Quantity] [int] NULL,

[StockValue] [float] NULL,

CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED

(

[EAN] ASC,

[Day] ASC,

[State] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.

My solution would be one of the following:

1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)

2. Partition the table

3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.

Does anyone have any thoughts on the matter?

Thanks!

Kev