Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rasmasyean
Starting Member
22 Posts |
 Posted - 2011-05-02 : 21:31:48
|
| If I had like a million rows and one column is a Foreign Key, if I do a WHERE on that FK, would it take long to return the results? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rasmasyean
Starting Member
22 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-05-02 : 22:06:51
|
| That's correct. The default for the PK is a clustered index, but it's not always the perfect candidate for the clustered index. FKs are almost always non-clustered indexes.I index ALL of my foreign keys regardless if they'll be queried or not. I don't trust developers when they say it'll never be used, so I index it to be safe. It's better to index it than not to index it. Of course it depends on your hardware if you can afford the additional upkeep that'll be needed for the extra index, but doesn't everyone have suitable hardware these days?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-05-03 : 11:22:28
|
| Also, the optimizer can make use of certain information to help optimize queries. So, for example if you are doing a join on a NON NULLable foreign key, the optimizer knows that a value must exist in the joining table and can make certain optimizations based on that knowledge. But, as mentioned, there is overhead associated with checking the foreign keys as well we maintaining indexes, if they exist. But, it's better to have slower correct data than really fast bad data. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-05-03 : 11:26:33
|
quote:
Originally posted by Lamprey
... But, it's better to have slower correct data than really fast bad data.
Whoa! Since when  Corey I Has Returned!! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-03 : 17:12:45
|
Lot's of people like to go with slow queries that return bad data.  Not having indexes on foreign keys is much more likely to cause application performance problems than having foreign keys that are not used.Developers might be able to say a foreign key index is not used now or for the purposes they thought of, but it doesn't mean it won't be used in the future.I had a vendor try to tell me recently that indexes were not required on the foreign keys in their database, but the fact that they had over than 170 foreign keys defined with no indexes on any of them let me know they were clueless about this. I ignored them, created the indexes anyway, and got a good performance improvment.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Is querying on using a Foreign Key efficient?