I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ million rows. I know this gets asked a lot, but I haven't found an answer that matches exactly what I'm trying to do.
CREATE TABLE [dbo].[Trades] ( [Id] [int] IDENTITY(1,1) NOT NULL, [TimeStamp] [datetime] NOT NULL, [Type] [int] NOT NULL, [Amount] [decimal](18, 2) NOT NULL, [Price] [decimal](18, 2) NOT NULL, [Exchange_Id] [int] NOT NULL )
As you can see, this is a table that stores commodity trades. Because of that, there are currently 100+ million rows in the table, with about 10,000 rows being added daily (at the end of the day).
I'm at a bit of a loss as to how to index this table to maximize performance. I don't so much care about insert performance since the table is updated once a day and it doesn't need to be a fast operation. Rows are also never updated once inserted. The big problem is query speed. This table is queried relatively often.
By far the most common queries are on the
[TimeStamp] column alone (eg, TimeStamp between two dates), or on the
[TimeStamp] column together with the
[Exchange_Id] column (eg, trades on a certain Exchange between two [TimeStamp]s).
Currently, there is a unique clustered index on the
[Id] column, but I'm not sure if that's the right thing to do based on the circumstances. I've considered making the clustered index on the
[TimeStamp] column, but it would have to be non-unique which I've read is generally bad. As for non-clustered indexes, I'm not quite sure how to arrange them or on what columns.
Any help is greatly appreciated.