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.

1) select salesorderid,CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid=776

2) select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid in (775,776,800) and orderqty>2

3) select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where modifieddate between '01-01-2010' and '01-01-2011'

4) select orderqty, unitprice,modifieddate from OrderDetail
where productid=776 and orderqty<4 and modifieddate between '01-01-2010' and '01-01-2011'

have tried creating indexes with combinations, but ended up with scans and lookups.

Thanks,

Shiva

• Merged by Kalman TothModerator 2 hours 39 minutes ago Duplicate
• Marked as answer by ShivaGS 2 hours 11 minutes ago

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.

1) select salesorderid,CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid=776

2) select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid in (775,776,800) and orderqty>2

3) select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where modifieddate between '01-01-2010' and '01-01-2011'

4) select orderqty, unitprice,modifieddate from OrderDetail
where productid=776 and orderqty<4 and modifieddate between '01-01-2010' and '01-01-2011'

have tried creating indexes with combinations, but ended up with scans and lookups.

Thanks,

Shiva

What are your current indexes that lead to scan and lookup? How many data you have in the table?

You may create an index with productid,modifieddate and orderqty and see your execution plan.

Create non-clustered index INX__OrderDetail__Productid__Modifieddate__orderqty

On OrderDetail(ProductId,Modifieddate,orderqty)

EDIT: The above suggestion is very wide, because without knowing your system and the table workload/data update scenarios, we will just be able to give on what you provided with. 

• Edited by Latheesh NKMicrosoft community contributor 3 hours 44 minutes ago

This is a duplicate thread:Plz use the other thread,

http://social.msdn.microsoft.com/Forums/en-US/1b10ddfc-66e0-4e1c-9724-c08358b17adf/index-on-multiple-columns-of-where-clause?forum=databasedesign

How big is a table? I would try CI on modifieddate , unique NCI productid and orderqty with INCLUDE clause on

salesorderid, CarrierTrackingNumber, unitprice

Please do not multipost I have already replied in another forum thread.

For your select statements 1,2&4, the best index should be
CREATE NON-CLUSTED INDEX INX_OrderDetail_Productid_Modifieddate_orderqty On OrderDetail(ProductId,Orderqty,Modifieddate) 
--where productid=776
--where productid in (775,776,800) and orderqty>2
--where productid=776 and orderqty<4 and modifieddate between '01-01-2010' and '01-01-2011'

For your select statements 3 the best index should be
CREATE NON-CLUSTED INDEX INX_OrderDetail_Modifieddate On OrderDetail(Modifieddate)
--where modifieddate between '01-01-2010' and '01-01-2011'

This is obviously not a production system. I say this because it does not make sense to me search in OrderDetails alone on ModifiedDate. Well, maybe in a date warehouse, but you would probably not have a rowguid column in your DW. :-)

And since this is not a real-world system, it all gets a little synthetic.

For a real-world system I would suggest that the primary is on SalesOrderID and RowNo. Oops there is no row number, just a SalesOrderDetailID. Maybe ProductID can join SalesOrderID in the PK. But that would add a business rule that the same product can only appear once in the table, and the column SpeicalOfferID makes me worried that this may not be the case.

So I guess that we have the clustered index on SalesOrderID alone, and make SalesOrderDetailID a nonclustered primary key.

ProductID is an obvious candidate for index. But if half of the orders include product 776, then you will get a scan for that query, and that is just the way it is.

The only other column that could be meaningful to index is SpecialOfferID. If nothing else that permits you to delete special offers when they have expired and all orders with it have been archived.

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.

1) select salesorderid,CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid=776

2) select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where productid in (775,776,800) and orderqty>2

3) select salesorderid,productid, CarrierTrackingNumber,orderqty, unitprice,modifieddate from OrderDetail
where modifieddate between '01-01-2010' and '01-01-2011'

4) select orderqty, unitprice,modifieddate from OrderDetail
where productid=776 and orderqty<4 and modifieddate between '01-01-2010' and '01-01-2011'

have tried creating indexes with combinations, but ended up with scans and lookups.

Thanks,

Shiva

• Merged by Kalman TothModerator Saturday, March 15, 2014 7:50 AM Duplicate
• Marked as answer by ShivaGS Saturday, March 15, 2014 8:17 AM

What are your current indexes that lead to scan and lookup? How many data you have in the table?

You may create an index with productid,modifieddate and orderqty and see your execution plan.

Create non-clustered index INX__OrderDetail__Productid__Modifieddate__orderqty

On OrderDetail(ProductId,Modifieddate,orderqty)

EDIT: The above suggestion is very wide, because without knowing your system and the table workload/data update scenarios, we will just be able to give on what you provided with. 

• Edited by Latheesh NKMicrosoft community contributor Saturday, March 15, 2014 6:45 AM