Hi Experts,
Need your advise to create indexes in the following scenario.
For example, we have a table OrderDetail with the following structure
CREATE TABLE [dbo].[OrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
We use this table in several select statements with various clauses.
select salesorderid,CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid=776
select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where orderqty>2
select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where modifieddate between '01-01-2026' and '01-01-2026'
select orderqty, unitprice,modifieddate from OrderDetail
where unitprice>10
select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where CarrierTrackingNumber like '2299%'
select salesorderid,CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where salesorderid=43659
have created 1 clustered+ 5 non-clustered covering indexes (total 6) on this table to avoid index scans and lookups. is this correct indexing strategy as i have created different indexes for different where columns?
Thanks,
Shiva