<p>In the recent project, we had an issue with the performance of few queries that relied heavily on ordering the results by datetime field (MSSQL 2008 database).</p>

<p>When we executed the queries with ORDER BY RecordDate DESC (or ASC) the queries executed 10x slower than without that. Ordering by any other field didn't produce such slow results.</p>

<p>We tried all the indexing options, used the tuning wizard, nothing really made any difference. </p>

<p>One of the suggested solutions was converting the datetime field to the integer field representing the number of seconds or miliseconds in that datetime field. It would be calculated by a simple algorithm, something like "get me the number of seconds from RecordDate to 1980-01-01". This value would be stored at insertion, and the all the sorting would be done on the integer field, and not on the datetime field. </p>

<p>We never tried it, but I'm curious what do you guys think?</p>

Are the inserts coming from .Net Code...
You could store the
DateTime.Ticks
value in a bigint column on the DB and index on that.
In terms of updating your existing Database, it should be relatively trivial to write a CLR Function for converting existing DateTimes to TickCount along the lines of
```
```
ALTER TABLE dbo.MyTable ADD TickCount BigInt Null

Update dbo.MyTable Set TickCount = CLRFunction(DateTimeColumn)
```
```
It definitely feasible and would dramatically improve your sorting abilility