January 30, 2011 at 10:58 pm
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.
January 31, 2011 at 12:48 am
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
January 31, 2011 at 2:08 am
January 31, 2011 at 6:11 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy