Hi Guys, Thanks for your responses... I have generalised the issue to make it more readable ...

The Reservation(actual name Requirement) Table def looks like this

ID [uniqueidentifier],

Kind [nvarchar] 255 Not Null, /* Kinds: Sale / Purchase */

ReservationNo [nvarchar] 16 Not Null,

ItineraryID [uniqueidentifier] Not Null, /* One Itinerary can have multiple Reservations */

BookedDate Datetime Not Null,

PickupDate Datetime Not Null,

DropOffDate DateTime Not Null,

PickUpLocationID [uniqueidentifier] Not Null, /* FK */

DropOffLocationID [uniqueidentifier] Not Null, /* FK */

RatePlanID [uniqueidentifier] Not Null, /* FK */

PromotionID [uniqueidentifier] Null, /* FK */

SalesPersonID [uniqueidentifier] Null, /* FK */

SellingCompanyID [uniqueidentifier] not null, /* FK */

AgentID [uniqueidentifier] not null, /* FK */

CustomerID [uniqueidentifier] not null, /* FK */

BookingValue Money not null,

ProductID [uniqueidentifier] not null,

CurrencyCode [nvarchar] 2 not null

..... and many more fields that are just value fields

Primary Clustered Index on [ID]

other Covering Indexs

-- Search for all reservations that belong to an Itinerary

1) Indexed Columns (

[Kind] ,

[ItineraryID]

)

INCLUDE ( [Id],

[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

--Search by reservation Number

2) Indexed Columns (

[ReservationNo]

)

INCLUDE ( [Id],

[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

-- Search by Customer Last Name &

-- Search by customer last name and Brand

3) Indexed Columns (

[Kind] ,

[CustomerID]

[SellingCompanyID]

)

INCLUDE ( [Id],

[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

-- Search by Booked Date or travel Dates or locations

4) Indexed Columns (

[Kind]

[BookedDate]

[PickupDate]

[DropOffDate]

[PickupLocationID]

[DropOffLocationID]

)

INCLUDE ( [Id],

[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

and this goes on...

One of the issues is we use Nhibernate to access the database... and I might be wrong but Nhibernate always access the whole object .... i.e select this_.ID, this_.ReservationNo..... (all columns) from Requirement where......

and due to this I am having to include all columns in the include column list of all indexes to avoid lookups...

Questions

1) would it be better for me to just have the ID as the included column so that SQL server uses the covering index and clustered index to retrieve the data.

2) Does SQL server use the included columns in a convering index to Search? or is this only a solution for lookups?

Yes I do keep a close eye on dm_db_index_usage_stats to make sure there are no unused indexes.

Guys, thanks again for taking the time to look into this.

Regards,

Ravi