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

Database Design - Creating Index


I want to create some index on a specific table to help boost performance for data retrieval.  The data would be searched on field1 and also on field2:

For example:
1) Where field1 = 1
2) Where field2 > 300
3) Where field1 = 20 and field2 > 400

Also, the two fields would be included in the order by clause (e.g. "order by field1, field2").

Should creating an index on field1 and a separate index on field2 is enough to take care everything stated above?  Or do I also have to create a combined index of (field1, field2) besides creating a separate index for field1 and field2?  What is the best approach for creating index in the above scenario?

  • 2
2 Solutions
Scott PletcherSenior DBACommented:
Separate indexes will likely be better if you use the command "WHERE col2 > value" fairly frequently.  If that search is rare, you should probably combine them as (col1, col2).

Either way col1 should probably be clustered.
I suggest creating an index on field1 by itself and another index on field2, field1 in that order.  So if the query only specified field1, it will use the first index.  If the query specified only field2, then it will use the second index.  And if both fields are specified, the second index will also be used.
Scott PletcherSenior DBACommented:
Actually if you cluster field1 you won't explicitly need to add it to the index on field2 -- SQL will automatically add it.

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.

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