I have scenario where the data will be loaded pretty much in the order it will be grouped by in reports (date, varchar foreign key, int foreign key). Is there still a benefit to indexing? I was going to define the run date as the clustered index, and index the two foreign keys. Since the data will be loaded via a process that will already have the data sorted, I wasn't sure if the benefit to the indexes still outweigh the overhead? I am really new at this. The table will probably be defined with a total of 4 fields, a max of 22,000,000 rows per run date, and retained for 60 days.
This is a complicated topic, I will try to be brief.
Benefit: Performance boost if implemented correctly
Drawback: requires understanding of clustered/nonclustered indexes and storage implications
Heaps (tables without clustered indexes) are fine for loading but can lead to poorly performing queries especially when dealing with millions of records as in your case. Even though you sorted your data prior to inserting, SQL Server does not know this and will simply put new records wherever there is room.
If there are not even any non-clustered indexes, queries against the heap will result in full table scans.
Assuming you are pre-sorting the data by datetime, and assuming that your date column is datetime, clustering on this column will result in extremely fast queries as long as they filter against the date. There are drawbacks, however, one of which being SQL Server will addding a 4 byte uniquifier to the index. Also any additional indexes defined will use the clustered index as the key, so you are in effect adding 12 bytes (8 bytes for datetime + 4 bytes for uniquifier) to the storage requirements for every value in the index.
I would add synthetic key instead. Make it a self incrementing integer value. Make it the primary key of the table and SQL Server will by default add a clustered index to that column. No uniquifier will be needed, so additional indexes will only require 4 bytes of storage for the key.
Note: varchar foreign keys can lead to poor performance as well. Change the base table to have an integer primary key instead.