When looking at the execution plan, it shows a index scan on coRooms.RoomName as is costing 41%, I'm assuming it is from this part of the query (really a subquery in the main query).
The coRooms table as a clustered index on the the RoomID (PK), as well as a normal index on deptID and RoomName (seperate indexes).
The meetings table as a nomral index on RoomID
Any idea's how to get the query to perform a seek vice scan?
Thanks
(SELECT Top 1 dbo.corooms.roomname
FROM dbo.meetings INNER JOIN
dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN
dbo.corooms ON dbo.meetings.roomid = dbo.corooms.roomid
where @ttstart between meetings.BeginTime and meetings.EndTime
AND meetproc.coperid=coPer.coPerID and meetings.isblock=0 and meetings.iscancelled=0) as RoomName,