I have a database that is used to store data collected from a number of channels. It is real time data so each data point has an associated DateTime value. In its most basic form it it looks like this:
CREATE TABLE [dbo].[TestTable]( [ChannelId] [int] NOT NULL, [DataTimeStamp] [datetime] NOT NULL, [Data] [float] NOT NULL )
I have a clustered index on the ChannelId and DataTimeStamp to allow retrieval of data over a given time span. This works well for queries but the index fragments, and rebuilding is getting slower as the table grows in size.
I've looked changing the fill factor but I think this would be effective only if data was inserted with a random DataTimeStamp, rather than monotonically increasing.
Is there anything I can do to reduce the fragmentation? The only option I can see would be to create a separate table for each channel.