July 30, 2008 at 8:37 am
Gail,
If you will become the DB designer then which model do you prefer??
I mean to say...a Covering Index on the table OR a Index on Foreign Key column??
Gail, As you may aware of...Index is not a preferable option for Foreign Key column as per Microsoft Standards....
Cheers!
Sandy.
--
July 30, 2008 at 8:45 am
Sandy (7/30/2008)
Gail,If you will become the DB designer then which model do you prefer??
I mean to say...a Covering Index on the table OR a Index on Foreign Key column??
Depends. Maybe the covering, maybe the key, maybe both, maybe neither. Depends on the queries that will be accessing the table and depends on the data in the table. As I've said before on similar threads, there are no hard and fast, always applicable rules for indexing. (Except test carefully)
Gail, As you may aware of...Index is not a preferable option for Foreign Key column as per Microsoft Standards....
Really? Want to give me a reference for that?
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 30, 2008 at 8:49 am
I thought it was fairly expected and common to put indexes on foreign keys (depending on needs of the queries used, etc., of course). I've never seen any documentation from MS suggesting otherwise.
"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
July 30, 2008 at 8:54 am
To be a bit more specific, when I'm considering indexes, what I will do is create the index that I think may be useful (perhaps one on a foreign key), then I will run all the queries that use that table and see if the new index improves their execution speed (which I would already have tested before). I will also look at the execution plans to see if any of the queries use the new index. I'll also examine the exec plan to see if any queries may use the index if it was wider.
If it does not appear that the new index is useful, I'll drop it and try a different option (perhaps the covering one).
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 30, 2008 at 9:09 am
Please Check this:
Cheers!
Sandy.
--
July 30, 2008 at 9:17 am
Yes, Gail...
Your last post make sense for me...
Thanks...:)
Anyways I would like to open this question for others who have already got the exposer to this type of indexing too...
Cheers!
Sandy.
--
July 30, 2008 at 9:20 am
From the first link:
So, I guess the only way round this is to disable the constraints - an option if your system is suffering from blocking or excessive reads caused by FK lookups.
But if your app isn't suffering from excessive blocks due to FK's you still need and want to use them appropriately, as Greg Low suggests in the second link. None of this is working against what Gail has suggested.
"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
July 30, 2008 at 9:28 am
Grant,
I am not talking about gail is rite or wrong...
I am looking for a answer which make sense with respect to performance and design prospective..
more over to that, if you also go through the MSDN, its clear that its often suggested you can use index on foreign key...
the word "often" makes me more interest to know what is correct...
I am also considering your suggestion what you said before post..(its depends on the need of the query..)
Still I am looking forward for a complete solution...
Cheers!
Sandy.
--
July 30, 2008 at 9:29 am
Sandy (7/30/2008)
Please Check this:
Those aren't official Microsoft Standards, they're blog posts by two MVPs.
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 30, 2008 at 9:32 am
Sandy (7/30/2008)
more over to that, if you also go through the MSDN, its clear that its often suggested you can use index on foreign key...the word "often" makes me more interest to know what is correct...
Still I am looking forward for a complete solution...
There is no absolute, always correct answer when it comes to indexing. There are just too many variables involved.
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 30, 2008 at 9:34 am
Gail,
As MVP's are the people those who selected by the Microsoft itself... So I can consider their suggestion's as one of the selective answer for this topic....
Cheers!
Sandy.
--
July 30, 2008 at 9:38 am
Sure, but don't consider them the official word from Microsoft, as that is not the case.
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 30, 2008 at 9:43 am
Thats True...I do agree on this...
but What's about the MSDN then...??
Cheers!
Sandy.
--
July 30, 2008 at 9:47 am
Sandy (7/30/2008)
Gail,As MVP's are the people those who selected by the Microsoft itself... So I can consider their suggestion's as one of the selective answer for this topic....
Cheers!
Sandy.
You might be interested to know that Gail is an MVP also. http://www.sqlservercentral.com/articles/SQLServerCentral.com/63590/
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 30, 2008 at 10:22 am
Although I was not knowing it..
but still I use to respect gail more than MVP...
His Answer is so specific to understand the technology like anything..
thanks to gail 🙂 for nice explanation and clear concept...
(Still i wants to know should i create index or not on the foreign key column.....he he he..lolz....:))
Cheers!
Sandy.
--
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply