July 29, 2008 at 9:11 am
Hi Members,
I just find one question from one of my team members
"Which type of index is creating when we creates a Foreign Key on a Column?"
And also if no index is on Foreign Key, then does it effects the Performance of query?
My question is: Is it advisable to create Index on foreign key or not?
If Yes..then which type.
If No, then why, I should not??
NB: All the Child table is having Foreign Key...
Cheers!
Sandy.
--
July 29, 2008 at 9:21 am
Sandy, in typical SQL Server speak the answer is "It depends". Look at the data in that column. Does it have a wide range of distinct values or a few distinct values. Does that table have a high row count? Is the column updated?
DAB
July 29, 2008 at 9:28 am
It is advisable to create nonclustered indexes on foreign keys, though whether the index should be just on the foreign key column, on other columns as well and whether it should include more columns depends on the data and queries run against the tables.
Indexes on foreign keys are a good place to start. Whether they are sufficient is another question. To answer that requires testing.
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
July 29, 2008 at 9:36 am
Gail, while I tend to agree I've run into some very bad choices at client sites. One company had a non clustered inde on their status column (foreign key to notification status table). It had 3 distinct values ( 'Q', 'D', 'R' for the Queued, Delivered, and Removed values in the status table) in over 500K rows. Additionally, as a "Queued" record was "Delivered" it's status was changed from "Q" to "D". Thus the index was reordered after each update. Execution plans showed table scans, fragmentation of 98% and selectivity of .0013. btw, this solution as proposed from Microsoft.
DAB
July 29, 2008 at 9:51 am
SQLServerLifer (7/29/2008)
Gail, while I tend to agree I've run into some very bad choices at client sites.
Haven't we all. 😉 It's a good place to start, nothing more. I don't think there are any hard and fast rules for indexing that always apply in every case.
In your case, I might have proposed a covering index on that foreign key, depending on the queries that were involved, how often the queries ran as opposed to the updates, and how often I could afford a rebuild.
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
July 29, 2008 at 9:56 am
GilaMonster (7/29/2008)
Haven't we all. 😉 It's a good place to start, nothing more.
July 29, 2008 at 10:06 am
There are no indexes created on Foreign Keys by default. Now, odds are you WILL want to have indexes that include foreign key columns. It really depends on what you are querying where you put the key. For example, if I have the tables:
[font="Courier New"]CREATE TABLE Persons
(
person_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
first_name VARCHAR(25),
last_name VARCHAR(25),
address_id INT FOREIGN KEY (address_id) REFERENCES Addresses(address_id)
)
CREATE TABLE Addresses
(
address_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
address_1 VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL.
state_province VARCHAR(50),
postal_code VARCHAR(15)
)
[/font]
If I always search on first_name, last_name and always return the address information I may want the index to be last_name, first_name, address_id
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 29, 2008 at 10:15 am
Just to add a tiny wrinkle to what everyone else said... I've frequently found situations where the foreign key was the best candidate for the clustered index. It was the most frequent access path for the data and was well distributed so that the grouping that comes with a clustered index benefited access to the tables nicely. But, as has already been stated, test, test, test, because, it depends.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 29, 2008 at 10:18 am
To extend your example, if there's a frequent query for all people living in a certain city, then I would probably want an index on the Address table on City (the address ID will be included automatically because it's the clustering key) and an index on the person table on Address_id include Firstname, surname
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
July 29, 2008 at 10:24 am
SQLServerLifer (7/29/2008)
GilaMonster (7/29/2008)
Haven't we all. 😉 It's a good place to start, nothing more.
That's why I prefaced mine with the ever popular disclaimer "It depends" 🙂
Last time I gave that answer in a discussion on indexing, I had 2 stress balls and a whiteboard marker thrown at me. 😀 :hehe:
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
July 29, 2008 at 11:52 am
To add another thing to think about...
If you do not index the foreign key field and you DELETE a record in your parent table, the delete will have to scan every record in the child table to determine if the foreign key has been violated.
So, if you have a very large table with foreign keys to smaller tables in which you will be expecting deletes, the index can be very helpful for your delete performance.
July 30, 2008 at 3:39 am
🙂 Ok, Nice to see all of your answers,
but as for my concerns and my question,
If you say "It depends" then please let me know the situation where I should go for Index on Foreign key where I should not??
Yes, One more thing, I bit confused what Michael Earl has explained in the last post. can anybody explain what does that mean??
To add another thing to think about...
If you do not index the foreign key field and you DELETE a record in your parent table, the delete will have to scan every record in the child table to determine if the foreign key has been violated.
So, if you have a very large table with foreign keys to smaller tables in which you will be expecting deletes, the index can be very helpful for your delete performance.
Cheers!
Sandy.
--
July 30, 2008 at 4:39 am
If you have a Company record with a CompanyID and a Person Record that has a CompanyID representing the company they currently work for, deleting any Comapny record would violate the foreign key if you had any people that currently have that CompanyID. To verify the foreign key has not been violated, the database engine needs to look up the ID for the deleted record to verify the ID has not been used.
July 30, 2008 at 4:44 am
Michael,
You mean to say if foreign key will have the Index then it will go for Index scan rather than table scan in the time of delete operation...rite??
so here my question is very clear ...that which type of index (clustered/nonclustered) do you prefer for this foreign key...?
hope you got my point??
Cheers!
Sandy.
--
July 30, 2008 at 6:27 am
Preferably an index seek. An index scan isn't that much better than a table scan.
Most of the time I prefer a nonclustered index, but there may be some cases where the foreign key is the preferred location for the clustered index. Depends on the data and on the queries you use.
Oh, and how you tell whether an index is useful or whether it should be clustered or nonclustered - test it and see.
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
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply