0

How to choose an index for the SQL Server table?

R (ID, name, salary, zipcode)

ID is the primary key (clustered index)

Queries:

select * 
from R 
where salary < 4000 and name = "john"

select * 
from R 
where id = 100 and zipcode = '76200'

select * 
from R 
where salary > 10000

I have an exam tomorrow and since it is paper pen exam I cannot check whether the indices I chose are correct or not. So I am stuck in this question.

We need to choose 2 other indices and mention

  1. which attribute form a search key
  2. index should be clustered or unclustered
  3. index should be hashed or B+ tree.

Could you please guide me on how to choose the index manually and justify the question above?

Thank you in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SrihariRaghu
  • 123
  • 2
  • 11

1 Answers1

2

this is a good reading about that matter that can help you to choose the index manually SQL Server Index Design Guidelines

and this SO link talk about clustering key, some point also mentioned in the above link picking a clustering key

Jeffry Evan
  • 317
  • 1
  • 7
  • Thank you. So it is like everyone has their own perception in choosing the index right ? I might choose Salary as clustered index (B+ tree) as it involves ranges and Zipcode as unclustered index as we cannot have range for it, it can be in Hash tree. Please correct me if I am wrong in this approach ? – SrihariRaghu May 08 '18 at 01:38
  • @SrihariRaghu from what i know, B-tree excels for range based searches and hash index better for strict equality. but AFAIK, sql server can only have 1 clustered index (and you already have that in your PK) so i'm not sure if you can have another clustered index – Jeffry Evan May 08 '18 at 01:57
  • I think we can have more than one clustered indices. say if i take age (say) as clustered indices. – SrihariRaghu May 08 '18 at 02:02
  • 1
    You can't have more than one clustered index, but you can select more than one column or else you can create multiple non-clustered index – Aswani Madhavan May 08 '18 at 03:35