indexes question

Assume we have a sales table, which contains a OrderID(char(8)) and a OrderDate(smalldatetime) field. Each day, hundreds of thousands of records needs to be inserted into this table. We need a daily sales report, and the OrderID is not necessarilly sequentially entered (every data operator has a range of OrderID, so for each data operator, it is sequential, but globally, it is not).

So, I would like to create a clustered index on OrderDate, and the Primary Key is on OrderID. I think it is good for generating the daily sales report. However, when an order is entered, we need to quickly check if the OrderID already exits, because the unique index on OrderID is built on the clustered index (on OrderDate), which means the Database Engine will search all the records on that day. This could be a slow process.

Is it possible to create indexes so that I can generate daily report and search for the OrderID quickly at the same time?

Thanks.

Create the clustered index on the orderDate and a second, unique non clustered index on orderId. When you check that the order id already exists, if you do a "if exists (select orderId from sales where orderId = @.orderId)", then this will not need to do a clustered index lookup as the index is covered i.e. : there is no need to go back to the base table, as the only column you are asking for (orderId) is already in the non clustered index.

The daily sales report would continue to work with the clustered index on the orderDate.

Hope this helps.