Databases
StackOverflow2013
Postsdbo
POIndexing strategy on table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIndexing strategy on table
    text
    copied!<p>I have an SQL Server 2005 table named 'EventTable' defined as such:</p> <p>EventID, EventTypeCode, EventStatusCode, EventDate</p> <p>Currently the table has a clustered index on the primary key 'EventID', there are no other indexes currently</p> <p>EventTypeCode and EventStatusCode columns are CHAR(3) (examples are 'NEW', 'SEN', 'SAL') and are foreign keys</p> <p>Common Selects will be...</p> <pre><code>select * from EventTable Where EventDate = @dateparam; select * from EventTable Where EventTypeCode = @eventtype; select * from EventTable Where EventStatusCode = @statustype; </code></pre> <p>What index strategy would you use to handle Select statements above?</p> <p>Is it better to have a covering (compound) index on the 3 columns? If so, what order should the compound index be in? </p> <p>Or a separate index on each of the 3 columns?</p> <p>The table will grow at the rate of about 300 events per day..</p> <hr> <p>It will also be common to execute queries such as <code><pre> where EventDate between '2008-12-01' and '2008-12-31' and EventTypeCode = 'todo' </pre> </code></p> <ul> <li>the table is more likely to grow at 500-800/records per day rather than 300</li> <li>the queries mentioned in the initial question will be run many times throughout the day, during normal use of the ASP.NET application</li> <li>NHibernate 'HQL' is used to perform such queries</li> <li>there is no initial load of data, the table only sits at about 10K records now because this is a new app</li> <li>...I'm more or less just trying to avoid the customer having to call us in a couple years to complain about the app becoming 'slow' since this table will be hit so much </li> </ul>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload