troubleshooting Question

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

Avatar of RCUllrich
RCUllrichFlag for United States of America asked on
Microsoft SQL Server
3 Comments1 Solution492 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros