Foreign Key with Clustered or Non-Clustered index

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  January 30, 2011 at 10:58 pm

  #230921

  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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 31, 2011 at 12:48 am

  #1280266

  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

• M&M

  SSC-Insane

  Points: 21700

  More actions

  January 31, 2011 at 2:08 am

  #1280284

  You could refer to below link.

  http://www.sqlmag.com/blogs/SQLServerQuestionsAnswered/SQLServerQuestionsAnswered/tabid/1977/entryid/72340/Should-I-Index-My-Foreign-Key-Columns.aspx

  M&M

• Grant Fritchey

  SSC Guru

  Points: 399059

  More actions

  January 31, 2011 at 6:11 am

  #1280363

  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