| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2011-04-12 : 02:53:08
|
| hello everybody, Is it a good practice to created NONCLUSTERED index on foreign key columns even if the parent table has few records like 100 to 500 records?thanx in advance |
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-04-12 : 03:34:29
|
| See it is depend on what data you are retrieve from table. Accordingly that you create Clust or non-Clust indexes.Raghu' S |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-12 : 06:26:45
|
| Typically, yes. Whether just an index on the foreign key is enough depends on the queries that you run.--Gail ShawSQL Server MVP |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-13 : 11:58:55
|
| If you do know that parent table will not grow, the index would not help you at all. However, if you don't know if it will grow, index won't hurt you either. So, better create one.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-14 : 16:34:35
|
quote:
Originally posted by tkizer
quote:
Originally posted by mmarovic
If you do know that parent table will not grow, the index would not help you at all.
What?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Huh, you are right. I somehow switched on thinking about the index on parent table instead of about the index on foreign key column in child table.So the correct answer is one GilaMonster gave. With 100 and more distinct values of a column the index on that column should be created. Even if there is no query having that column value as a part of criteria, the index would help deletes and updates of rows from the parent table, because index would be used for foreign key constraint validation.Tara, thank you for the correction.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-15 : 03:03:02
|
quote:
Originally posted by tkizer
It still doesn't matter if it's the parent or child. Lack of data growth doesn't mean that you don't have to index.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
If it is a parent table with only 100 rows, then you do not need index to retrieve data from there. If you know it will never grow to tens or hundreds of thousands rows, from reading perspective you don't need an index.However, I forgot that we were talking about foreign key. That means the column in parent table must be primary key, so an index will be there anyway.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-15 : 04:08:10
|
quote:
Originally posted by tkizer
No matter the size, I still index all of my foreign keys. It doesn't hurt to have them there for the small tables, and means my report will show good when I'm verifying my FKs are indexed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blogI agree. As I explained I somehow kept thinking about small number of rows, forgetting it was a foreign key column.
I agree. As I explained, I somehow kept thinking about small number of rows forgetting it was about foreign key column.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-15 : 04:51:30
|
quote:
Originally posted by mmarovic
If it is a parent table with only 100 rows, then you do not need index to retrieve data from there.
There's no single magical figure below which an index is not needed. Technically if the entire table fits onto a single page, an index will have no effect, but that's not a fixed magical 100 rows.--Gail ShawSQL Server MVP |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-15 : 05:17:47
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by mmarovic
If it is a parent table with only 100 rows, then you do not need index to retrieve data from there.
There's no single magical figure below which an index is not needed. Technically if the entire table fits onto a single page, an index will have no effect, but that's not a fixed magical 100 rows.--Gail ShawSQL Server MVP
Sure. The number of 100 rows was mentioned in question posted. I never said that is the number deciding about index, just that for that number of rows index will not help performance.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-15 : 05:36:33
|
| [code]CREATE TABLE testingIndexes ( ID INT IDENTITY, Somedate DATETIME, Filler CHAR(2000) -- simulating the rest of the columns in the table)INSERT INTO testingIndexes (SomeDate, Filler)SELECT TOP 90 DATEADD(dd, column_id, GETDATE()), ' ' FROM sys.columnsSET STATISTICS IO ONGOSET STATISTICS TIME ONGOSELECT * FROM testingIndexes WHERE ID = 56GO/*Table 'testingIndexes'. Scan count 1, logical reads 23, physical reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.*/CREATE INDEX idx_Testing ON testingIndexes (ID)GOSELECT * FROM testingIndexes WHERE ID = 56GO/*Table 'testingIndexes'. Scan count 1, logical reads 3, physical reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.*/[/code]Major improvement? No, certainly not. Improvement, yes. (looking at the reads, the CPU and duration are both too small to be measured accurately by Statistics Time) May be important if that query is run hundreds of times a minute, or if the table is part of a larger query (foreign key or not)There is no hard minimum row count where indexes make no difference. It's up to testing the individual scenarios and seeing. If the above example was in my system I might index it or I might not, depending on how much of an impact that improvement meant, how important that query is, how often it's run.--Gail ShawSQL Server MVP |
|
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-04-15 : 05:52:01
|
| Hey Friends good discussion on IndexesRaghu' S |
|
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-15 : 14:16:36
|
| Its depend on the table operation ,Index always create on the table which one have less insert and update operations.First you just to know this foriegn key is using in any where clause if not then you dont need to make a nonclustered on itRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-15 : 14:28:06
|
quote:
Originally posted by JahanzaibFirst you just to know this foriegn key is using in any where clause if not then you dont need to make a nonclustered on it
Or if it's in a join somewhere.--Gail ShawSQL Server MVP |
|
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-16 : 00:35:04
|
| Yes you will create on predicates also likeBETWEEN CONTAINS EXISTS IN IS LIKE Search Condition like =,<,>,>=,<= etc these all will use in where and having clauseRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
|
|
|
|
indexing