What's wrong with Datetime fields? Why indexes on those fields are not recommended?
I use to work with big tables containing records about some events. And main part of information is Start Date, End Date, Date Scheduled, etc.
Typical query: select Cols from TABLE where Date between @StartDate and @EndDate.
It takes ages to run this query unless you create clustered index on Datetime column. Non clustered index helps but not really much. But clustered index dramatically improves performance, especially if you are interested in short period between @StartDate and @EndDate - e.g. one month from 30 years log.
That’s why I really surprised by suggestion not to use indexes on Datetime columns. If there is any reasonable explanation to this?
And another notice for INSERT, DELETE and UPDATE. If you choose right indexes policy this operations may not cause significant delays because of indexes. For example, if you delete solid range of rows arranged by clustered index (delete from TABLE where INDEXEDCOL between A and B) it will take the same time as delete up to 10 rows depending on fragmentation level of your table. Because it will delete rows only from 2 pages (where A and B values are stored) and then delete pointers to PAGES.
But if you have 10 other indexes on the same table it won’t really help. Those indexes will eat all your time.