I did test in this way:
create table:
create table dbo.orders
(
id int identity,
orderDate datetime,
orderType int,
orderAmount money,
orderDiscount money
);
populate it with data:
declare @i int=0;
while @i<100000
begin
insert dbo.orders(orderDate, orderType, orderAmount, orderDiscount)
values(
DATEADD(mi, -1*ABS(CHECKSUM(NEWID()) % 10000), GETDATE()),
ABS(CHECKSUM(NEWID()) % 100),
0.01*ABS(CHECKSUM(NEWID()) % 10000),
0.01*ABS(CHECKSUM(NEWID()) % 1000)
);
set @i=@i+1;
end
creating indexes:
create index I1 on dbo.orders(orderDate, orderType) include(orderAmount);
create index I2 on dbo.orders(orderDate) include(orderType, orderAmount);
test using queries:
set statistics time on;
set nocount on;
select orderType, SUM(orderAmount)
from dbo.orders with (index(I1))
where orderDate>'20130501'
group by orderType
select orderType, SUM(orderAmount)
from dbo.orders with (index(I2))
where orderDate>'20130501'
group by orderType
Result:
SQL Server Execution Times:
CPU time = 312 ms, elapsed time = 315 ms.
SQL Server Execution Times:
CPU time = 296 ms, elapsed time = 298 ms.
Without index hint, SQL Server chose I2 index. Anybody can explain this behavior?