Link to home
Start Free TrialLog in
Avatar of RCUllrich
RCUllrichFlag for United States of America

asked on

In SQLServer2012, are explicit Indexes needed in addition to Foreign Key Constraints?

I am in the process of moving several Access backend databases to SQLServer 2012. I have scripts that will create the tables and define the primary key and any foreign keys. Do I also need to add an index on the Foreign Key or is that automatically built when I add the CONSTRAINT for the FOREIGN KEY?
Here is the syntax of the Create Table command:

CREATE TABLE [dbo].[AssetGroup] (
    [asset_grp_num]      INT                      IDENTITY (1, 1) NOT NULL,
    [asset_grp_name]     VARCHAR (255) NOT NULL,
    [is_inactive]              BIT                       DEFAULT ((0)) NOT NULL,
    [emp_id]                   INT                       NOT NULL,
    [mod_dt]                  DATETIME            DEFAULT (GetDate()) NOT NULL,
    CONSTRAINT [PK_AssetGroup] PRIMARY KEY CLUSTERED ([asset_grp_num] ASC),
    CONSTRAINT [FK_AssetGroup_Employee] FOREIGN KEY ([emp_id]) REFERENCES [dbo].[Employee] ([emp_id])
);

Here is the syntax of the Index that is related to Foreign Key defined above:

CREATE NONCLUSTERED INDEX [IX_AssetGroupEmployee]
  ON [dbo].[AssetGroup]([emp_id] ASC);

I am assuming that the index on the foreign key would only be needed if this index was needed to optimize queries. Or is it a good practice to always add an index for any Foreign Key?
Thanks,
Bob
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RCUllrich

ASKER

Thanks for your reply.

It is important that my tables have relational integrity, so my question is:
Is an index for all foreign keys required or optional?

If a table and its related table have few records, then could the index on the foreign key be unnecessary?
Is an index for all foreign keys required or optional?
Optional.

If a table and its related table have few records, then could the index on the foreign key be unnecessary?
It depends on your definition of "few", but in general yes.  But as always your miles may vary.