sql server - Query Performance Paradox: Unique Clustered vs Non-Unique Non-Clustered Index -

i have table my_table approximately 9 million rows.

there in total of 38 columns in table. columns relevant question are:

• record_id: identity, bigint, unique clustered index
• record_created: datetime, non-unique & non-clustered index

now run following 2 queries , naturally expect first 1 execute faster because data being sorted column has unique clustered index somehow executes 271 times(!) slower.

select top 1      record_id       my_table       record_created >= '20140801'  order      record_id  select top 1      record_id       my_table       record_created >= '20140801'  order      record_created 

the execution times 1630 ms , 6 ms, respectively.

please advise.

p.s.: due security policies of environment cannot see execution plan or use sql profiler.

sql server has few choices make how perform query. could begin sorting items, leveraging indexes mentioned, , follow filtering out items don't match clause. however, it's typically faster cut down on size of data set you're working first, don't have sort many items.

so sql server most-likely choosing perform where filter first. when this, starts using non-unique, non-clustered index on record_created skip on items record_created less '20140801', , take items after that.

at point, items pre-sorted in order in found in record_created index, second query requires no additional effort, first query has perform sort on records have been chosen.