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?
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?