Foreign Key with Clustered or Non-Clustered index

  • Hi, I would like to understand the concept of foreign key with an index. Is there any impact on Foriegn key with clustered index or non-clustered index?

    Eg:

    Create Table T1(Col1 Int Primary Key, Col2 Int)

    Create Table T2(Col1 Int references T1(Col1), Col2 Int)

    Insert into T1 Select 1,100

    Insert into T1 Select 2,200

    Insert into T1 Select 3,300

    Insert into T1 Select 4,400

    Insert into T1 Select 5,500

    Create Table LT1(Col1 Int Unique , Col2 Int Primary Key)

    Create Table LT2(Col1 Int references LT1(Col1), Col2 Int)

    Insert into LT1 Select 1,100

    Insert into LT1 Select 2,200

    Insert into LT1 Select 3,300

    Insert into LT1 Select 4,400

    Insert into LT1 Select 5,500

    --Switch on Execution Plan

    SET SHOWPLAN_TEXT ON

    Insert into T2 Select 1,100

    Insert into LT2 Select 1,100

    If we look at the plan, it looks as the below:

    |--Assert(WHERE:(CASE WHEN NOT [Pass1008] AND [Expr1007] IS NULL THEN (0) ELSE NULL END))

    |--Nested Loops(Left Semi Join, PASSTHRU:([Cognizant20_Helpdesk].[Cog2L2Write].[T2].[Col1] IS NULL), OUTER REFERENCES:([Cognizant20_Helpdesk].[Cog2L2Write].[T2].[Col1]), DEFINE:([Expr1007] = [PROBE VALUE]))

    |--Table Insert(OBJECT:([Cognizant20_Helpdesk].[Cog2L2Write].[T2]), SET:([Cognizant20_Helpdesk].[Cog2L2Write].[T2].[Col1] = [@1],[Cognizant20_Helpdesk].[Cog2L2Write].[T2].[Col2] = [@2]))

    |--Clustered Index Seek(OBJECT:([Cognizant20_Helpdesk].[Cog2L2Write].[T1].[PK__T1__A259EE542FCAE5A8]), SEEK:([Cognizant20_Helpdesk].[Cog2L2Write].[T1].[Col1]=[Cognizant20_Helpdesk].[Cog2L2Write].[T2].[Col1]) ORDERED FORWARD)

    |--Assert(WHERE:(CASE WHEN NOT [Pass1008] AND [Expr1007] IS NULL THEN (0) ELSE NULL END))

    |--Nested Loops(Left Semi Join, PASSTHRU:([Cognizant20_Helpdesk].[Cog2L2Write].[LT2].[Col1] IS NULL), OUTER REFERENCES:([Cognizant20_Helpdesk].[Cog2L2Write].[LT2].[Col1]), DEFINE:([Expr1007] = [PROBE VALUE]))

    |--Table Insert(OBJECT:([Cognizant20_Helpdesk].[Cog2L2Write].[LT2]), SET:([Cognizant20_Helpdesk].[Cog2L2Write].[LT2].[Col1] = [@1],[Cognizant20_Helpdesk].[Cog2L2Write].[LT2].[Col2] = [@2]))

    |--Index Seek(OBJECT:([Cognizant20_Helpdesk].[Cog2L2Write].[LT1].[UQ__LT1__A259EE5538602BA9]), SEEK:([Cognizant20_Helpdesk].[Cog2L2Write].[LT1].[Col1]=[Cognizant20_Helpdesk].[Cog2L2Write].[LT2].[Col1]) ORDERED FORWARD)

    My question is even the Col2 is being used heavily for querying a range of data, would be there any impact of the above indexes on referential check as it is been used heavily for constarint for more than 10 table that the type of index?

    Help me to understand better.

  • Normally you'd start with a nonclustered index on the foreign key columns, then modify if necessary.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • My general approach is a little different than others. I tend to cluster on the most frequently accessed path into the data that is also selective enough to index well. In lots of cases, that defines the foreign key for a table since the PK may not be the most frequently used access path. But it really depends on the system, the data, and how it's being retrieved.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply