0

I have table as follows

Bank | MinValue | MaxValue

I have to run a query to find all banks which have min value less than 5 and max value greater than 5.

How should I perform indexing in such scenario? Thanks in advance for any advice.

my question is not really about when I should use indexing ( consider this scenario to have many retrievals than insertions). Given that, what would be the best approach to index. I am thinking of using clustered index on one column and non clustered index on other column.

KItis
  • 5,476
  • 19
  • 64
  • 112
  • It depends on the data size, if there're millions of records in the db while you need the dbms to return the result within a short interval, then you'd better create index for both MinValue & MaxValue. As as side-effect, more indexes also means lower modification operations (insert/update/delete). – tibetty Aug 15 '17 at 15:14
  • should I use clustered index or single column index – KItis Aug 15 '17 at 15:28
  • It depends on your data, how often you perform this query, and so forth. If it is an occasional query, and the number of returned rows is unknown or large, and there are no other fields, you don't need an index, a table scan will be nearly as good. – Ben Aug 15 '17 at 15:33
  • 2
    There's only one clustered index (records are physically ordered per this index) for each table, so it's better to leave it to a field on which range related queries happens the most frequently. – tibetty Aug 15 '17 at 15:33

2 Answers2

0

If you don't often insert new rows into the table, so creating an index on "value" field would be a good choice and will speed up the query.

If you're doubting in creating an index, read this When should I create database indexes?

Anton Bondar
  • 319
  • 2
  • 4
  • 13
  • my question is not really about when I should index, consider that this is a scenario that requires indexing. Given that, what would be the best approach to index. I am thinking of using clustered index on one column and non clustered index on other column. – KItis Aug 15 '17 at 15:42
  • @KItis It depends on your future needs. From the task, that you've describe, it's enough to make non clustered index on one column. – Anton Bondar Aug 15 '17 at 16:59
0

Creating an Index on MinValue, MaxValue, INCLUDE Bank, would cover your query.

Whether or not that index should be clustered depends on other factors outside the scope of this question.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52