SQL Server 2005 Foreign Keys and Indices

Question

I have a general question regarding table indices on foreign keys in database modeling. If I have a table, TABLE_A, created as:

CREATE TABLE [dbo].[TABLE_A](
 [ID] [int] IDENTITY(1,1) NOT NULL,
    [RelatedTableBID] [int] NOT NULL,
    CONSTRAINT [PK__TABLE_A] PRIMARY KEY CLUSTERED 
    (
      [ID] ASC
     ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,  
     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)   
     ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[TABLE_A]  WITH CHECK 
ADD  CONSTRAINT [TABLE_A__RelatedTableB]     
FOREIGN KEY([RelatedTableBID])
REFERENCES [dbo].[TABLE_B] ([ID])

and Table_B as:

CREATE TABLE [dbo].[TABLE_B](
 [ID] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK__TABLE_B] PRIMARY KEY CLUSTERED 
    (
      [ID] ASC
     ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,  
     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)   
     ON [PRIMARY]
) ON [PRIMARY]

Will it in practice be more efficient if I create an index on the referencing table (Table A) referencing column (RelatedTableBID)? As in:

CREATE INDEX TABLE_A_FK_INDEX1 on TABLE_A(RelatedTableBID)

Or am I thinking about this backwards? It seems that since the column being referenced is itself a clustered index, there shouldn't be any issues during joins - if anything, only deletes from TABLE_B seem susceptible to poor performance.

Thanks for any help in setting me straight.

-Mustafa

EDIT
So, in general, if I'm ever joining on or using a column in a where clause consistently when querying, should I consider adding an index on it? What are some best practices and "rules of thumb" for creating database indexes? Sounds like that's a generally sound decision.

Answer 1

You've got it right. That index on the foreign key should help whenever you need to join TABLE_A and TABLE_B.

Answer 2

No. What you're looking for is a third table. If it's a many-to-many relationship you're looking to build, it's not possible to do without a disgustingly ineffecient DB design without a third table.

Thus: Table 'users': uid, first, middle, last Table 'addresses': aid, street, city, state, country, etc. Table 'users-addresses': id, uid, aid

Then you can make many to many associations in the third table. This is typically called database normalization.

So then your query associative query would look something like:

SELECT * FROM users,addresses JOIN users-addresses ON users.uid=users-addresses.uid AND addresses.aid = users-addresses.aid WHERE users.uid='1' or something like that

..would give you all user information and addresses for user '1'