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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 indexing

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
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-14 : 15:44:41
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-14 : 16:40:29
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-15 : 03:16:11
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 Shaw
SQL 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.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

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.columns

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT * FROM testingIndexes WHERE ID = 56
GO

/*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)
GO

SELECT * FROM testingIndexes WHERE ID = 56
GO

/*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 Shaw
SQL Server MVP
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 05:52:01
Hey Friends good discussion on Indexes

Raghu' S
Go to Top of Page

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 it

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-16 : 00:35:04
Yes you will create on predicates also like

BETWEEN
CONTAINS
EXISTS
IN
IS
LIKE
Search Condition like =,<,>,>=,<=

etc these all will use in where and having clause


Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page
   

- Advertisement -