• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Help with a Index on MS sql server

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 ?
0
Giambattista
Asked:
Giambattista
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need a covering index in this order  idhotel,idroom, date_to, ..
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I'd recommend using two indexes: one unique clustered index including , in the following order:  IDHOTEL - IDROOM - DATE_FROM - DATE_TO and a non clustered index on ID
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now