CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
  ON Sales.SalesOrderDetail (ProductID,ModifiedDate)
  INCLUDE (OrderQty,UnitPrice,LineTotal,SpecialOfferID) -- NOTE the addition of the predicate-column)
  WHERE SpecialOfferID <>1
 

SELECT  ProductID ,
        ModifiedDate ,
        SUM(OrderQty) 'No of Items' ,
        AVG(UnitPrice) 'Avg Price' ,
        SUM(LineTotal) 'Total Value'
FROM    Sales.SalesOrderDetail
WHERE   SpecialOfferID = 2
GROUP BY ProductID ,
        ModifiedDate

...WITH (INDEX(FK_ProductID_ModifiedDate)) WHERE   SpecialOfferID = 2 ...

if OBJECT_ID('dbo.test') is not NULL
     drop table dbo.test

create table dbo.test (i int primary key,
     isinactive bit
)

create index FIX_Test_Active on dbo.test (i)
     where isinactive = 1
go
with i as (select top 8000 ROW_NUMBER() over (order by a.object_id) id
     from sys.columns a, sys.columns b)

insert into test
     select id,
          case 
               when (id % 2) = 0 then 1
               else 0
          end
     from i

select i from dbo.test where isinactive = 1