Are foreign keys indexed automatically in SQL Server?

SQL Server will not automatically create an index on a foreign key. Also from MSDN:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

As I read Mike's question, He is asking whether the FK Constraint will create an index on the FK column in the Table the FK is in (Table1). The answer is no, and generally. (for the purposes of the constraint), there is no need to do this The column(s) defined as the "TARGET" of the constraint, on the other hand, must be a unique index in the referenced table, either a Primary Key or an alternate key. (unique index) or the Create Constraint statment will fail.

(EDIT: Added to explicitly deal with comment below -) Specifically, when providing the data consistency that a Foreign Key Constraint is there for. an index can affect performance of a DRI Constraint only for deletes of a Row or rows on the FK side. When using the constraint, during a insert or update the processor knows the FK value, and must check for the existence of a row in the referenced table on the PK Side. There is already an index there. When deleting a row on the PK side, it must verify that there are no rows on the FK side. An index can be marginally helpful in this case. But this is not a common scenario.

Other than that, in certain types of queries, however, where the query processor needs to find the records on the many side of a join which uses that foreign key column. join performance is increased when an index exists on that foreign key. But this condition is peculiar to the use of the FK column in a join query, not to existence of the foreign Key constraint... It doesn't matter whether the other side of the join is a PK or just some other arbitrary column. Also, if you need to filter, or order the results of a query based on that FK column, an index will help... Again, this has nothing to do with the Foreign Key constraint on that column.

No, creating a foreign key on a column does not automatically create an index on that column. Failing to index a foreign key column will cause a table scan in each of the following situations:

• Each time a record is deleted from the referenced (parent) table.
• Each time the two tables are joined on the foreign key.
• Each time the FK column is updated.

In this example schema:

CREATE TABLE MasterOrder (   MasterOrderID INT PRIMARY KEY)CREATE TABLE OrderDetail(   OrderDetailID INT,   MasterOrderID INT  FOREIGN KEY REFERENCES MasterOrder(MasterOrderID))

OrderDetail will be scanned each time a record is deleted in the MasterOrder table. The entire OrderDetail table will also be scanned each time you join OrderMaster and OrderDetail.

   SELECT ..   FROM       MasterOrder ord      LEFT JOIN OrderDetail det       ON det.MasterOrderID = ord.MasterOrderID   WHERE ord.OrderMasterID = @OrderMasterID

In general not indexing a foreign key is much more the exception than the rule.

A case for not indexing a foreign key is where it would never be utilized. This would make the server's overhead of maintaining it unnecessary. Type tables may fall into this category from time to time, an example might be:

CREATE TABLE CarType (   CarTypeID INT PRIMARY KEY,   CarTypeName VARCHAR(25))INSERT CarType .. VALUES(1,'SEDAN')INSERT CarType .. VALUES(2,'COUP')INSERT CarType .. VALUES(3,'CONVERTABLE')CREATE TABLE CarInventory (   CarInventoryID INT,   CarTypeID INT  FOREIGN KEY REFERENCES CarType(CarTypeID))

Making the general assumption that the CarType.CarTypeID field is never going to be updated and deleting records would be almost never, the server overhead of maintaing an index on CarInventory.CarTypeID would be unnecessary if CarInventory was never searched by CarTypeID.