I have table with Orders with unique incrementing ID and (order creation) Date. This table is quite large and wide (5mn rows, 50 columns, 10 columns of which are FK IDs). Example:
CREATE TABLE Orders As ( ID int UNIQUE AUTOINCREMENT (1,1), DATE datetime, --This is SQL Server 2005, so no [date] data type OrderStatus char (2), ClientID int ... )
I get this table as copy of yesterday's production table. Table (as whole DB) is used for reporting purposes and therefore read-only.
I have 2 very common use cases:
- 80% (or more) of queries have
WHEREclause, as users want data for specific business date.
Here I want to create clustered index on DATE.
- 40%-60% of queries use
JOINOrders table to other tables with information about Order details (Product, Supplpier, Payments, Reservations, etc).
Here I want to create clustered index on OrderID.
Can I have one CI for both cases?
Index (ID, DATE) will not work with
Index (DATE,ID) will not work for joins on
ID only or
Catch. We know that DATE is incremental as well as ID. ID with higher value cannot appear yesterday under any circumstances as it is
Question. Is there a way to tell SQL Server that CI (DATE, ID) will have ID sequentially ordered for all dates?
My only solution at the moment is crating non clustered covering index (ID, DATE), but it's suboptimal.
I've searched for some time, but could not find anything. If there is a solutions for later versions of SQL Server I would be interested in it too.
I know clustered index basics. Please note, database is put in read-only state for users.
Logically you can just ignore Date column in (Date, ID) index without any harm at all. Possibly this is a very specific use case that is not yet covered by SQL Server functionality.