I have the following table in my database:
create table [dbo].[tb_StatusLog]( [RequestID] [int] not null, [statusID] [int] not null, [startTime] [datetime] not null, [endTime] [datetime] null, )
with the following non-clustered index
create nonclusteredindex [index1] on [dbo].[tb_StatusLog] ( requestID asc, statusID asc ) include ([startTime])
and the following table-valued function
select requestID, max(startTime) from tb_StatusLog where statusID=2 group by requestID, statusID
after running profiler trace and running the results by db engine tuning advisor, it proposed that I create the following index (which is the same index I have but with the key columns reversed):
create nonclusteredindex [index2] on [dbo].[tb_StatusLog] ( statusID asc, requestID asc ) include ([startTime])
Now, when running
fn_getTable, the execution plan uses
index2 instead of
index1 and the performance improved.
Why is that?