Should i add a non clustered index over a clustered index table, based on this where clause and join?

1
votes

I have a parent table and a child table.

The parent table has a clustered index as primary key with increment value (ParentID). The child table also has a clustered index as primary key with increment value (ChildID)

Primary key Parent.parentID is in relation with child.parentID as a foreign key.

I join those two tables based on following query.

Select ....
Join on parent.parentID = child.parentID 
where parent.personalNumber = 197608134356 <-- varchar

Now, should I

  1. add non clustered index on parent.personalNumber as it is in the where clause?
  2. Add a non clustered index on the foreign key child.parentiD to speed up the join?

It would mean I put non clustered index over a clustered index table.

I expect a lot of rows on both parent and child over time. There will be inserts and selects. No updates or deletes

Thanks /s

3
sqlsql-serversql-server-2008tsql
My suggestion is to add an index to personalNumber. What about the child.parentID it is depend on what type of join is used by optimizer. Usually, to add the index on child.parentID is good idea. – Hamlet Hakobyan
I would definitely add the nonclustered index on child.ParentID to help with the JOINs. Whether or not the NC index on parent.PersonalNumber will help depends on (a) how selective that PersonalNumber is (with a given value, how many % of the original rows will be retrieved?) and (b) what kind of columns you have in your SELECT clause (if you use SELECT * everywhere, then the usefulness of a NC index is significantly reduced) – marc_s

3 Answers

0
votes

You can have only one clustered index on a table, and you already have one on ParentId for Parent table and one for ChildId on Child table, both are incremented values wich is good, and primary keys which is good too (not mandatory, you could choose to have your clustered index on other columns and a non clustered index on your pk).

Your design looks fine. You have to add non clustered indexes on your search columns (parent.personalNumber and others if any) and on foreign keys, it usually helps.

0
votes

Use clustered index for PK unless you have a compelling reason not to.

That join can still use a clustered composite PK on child
Check the execution plan - I would be very surprised if that clustered index was not used by the join

An index on the personalNumber should aid the where

0
votes

If you are designing for high performance, with many concurrent clients, all inserting into your table at the same time - you should not have a clustered index based on a PK that is also an identity column. See this article for an explanation. To do so creates a hotspot in your table which can adversely impact performance.

There is almost always a more appropriate column to base a clustered index on in a table with an identity column as a PK.