Database clustered Index on referring column

Question

I have two tables like this.

Table A

A_ID ( Primary Key)
A1
A2

Table B

B_ID ( Primary Key)
B1
B2 A_ID ( foreign key but not enforced in database, not unique )

Although by default SQL server creates clustered indexes on B_ID, I have lot of select queries on B, which depend on A_ID

something like this

SELECT * FROM B WHERE B.A_ID = 'SOME ID'

Should I be creating clustered Index on A_ID of TABLE B ?

Answer 1

No, just create a normal non-clustered index - you'll have basically the same results and same improvements in your query speed.

Is the A_ID on table B even guaranteed to be unique?? Couldn't more than 1 entry in "B" reference the same A_ID ??

Best practice for a clustered key is:

• as small as possible (narrow)
• unique
• stable (or static - it should never change)
• ever increasing

See Kim Tripp's The Clustered Index Debate continues or Ever-increasing clustering key - the Clustered Index Debate - agin! for additional info.

If your clustered key cannot be guaranteed to be unique, SQL Server will add a 4-byte uniquifier to it - you'll want to avoid that whenever possible (because your clustering key will be added to every single entry of every single non-clustered index on your table, leading to waste of space if it's too wide).

Marc

Answer 2

No a regular non-clustered index should do fine. A clustered index is especially handy when doing range queries (BETWEEN) As a rule of thumb I always create non-clustered indexes on columns used in foreign key constraints.