Help with a Index on sql server

[Giambattista Scivoletto]

I have a table named AVAILABILITY

ID - IDHOTEL - IDROOM - DATE_FROM - DATE_TO

for each room a hotel insert a period (from - to) when the room in booked

Considering this query

select id, date_from, date_to from AVAILABILITY

where IDHOTEL = #idhotel#

   and IDROOM = #idroom#

   and DATE_TO >= #dateadd("d",-1,now())#

order by DATE_FROM

Could you suggest me a index to create on the table ?

[Dan_Bracuk]

What are the existing indexes?

[Giambattista Scivoletto]

Actually I have these:

unique clustered index idhotel, idroom, date_from, date_to

non clustered index ID

[Dan_Bracuk]

Why do you think you need another one?  Is your query slow?  Using cfqueryparam will speed it up.

[Giambattista Scivoletto]

The query isn't slow, but actually database in pretty empty. I want to be sure that it is the best index I could choose.

Could you give me, please, a hint how you would use cfqueryparam in the query? Sorry, I'm not so good in it.

[Owainnorth]

Depending on how many rows are in there, you may find the database engine doesn't actually bother using the index at all, it's not until you have thousands of rows it really starts to make a difference.

Weigh that up with the amount it'll slow down inserts, and you might find it detrimental to create an index. Definitely put in queryparams though, there's never an excuse for not doing that and as Dan says that'll speed things up a bit.