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 |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-09 : 04:36:55
|
| Is it necessary to place an index on a table which has about 50 records? This table does have a primary key which is a varchar(3) column. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-11-09 : 04:39:30
|
Will the number of rows increase?My opinion: there is no need to create an index on a table with 50 rows.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-09 : 04:40:04
|
| it has pk means it should have clustered index (unless you specify as non clustered). since its small table, i dont adding another index will do much benefit------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-09 : 04:41:27
|
| It probably won't help disk IO (depending on the size of the rows) but it might help with loop processing. A friend had an example with a small table where a clustered index helped - but that was accessing millions of times for a query - also was before R2 so might not apply now.So probably wouldn't be used other than in exceptional circumstances - and if you were working on that sort of system you probably wouldn't need to ask.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-09 : 07:18:17
|
| Ok, thanks guys |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-11-09 : 12:04:41
|
| But then again why not. Maybe your table will grow so good to be proactive. We like to index FKs so if there is an FK on the table, why not add an index on it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-09 : 15:07:56
|
| We have a house rule, and thus rarely broken, to always have a clustered index. By default a PRIMARY KEY will use the clustered index, so defining a Primary Key is "kinda" the same thing.This prevent the table being a heap, and ensures that table-housekeeping can reorganise the table etc.But for 50 rows its usually irrelevant - provided that it doesn't grow for some reason (and if it does grow WHO is going to remember that the table has no clustered index and NOW needs that adding??? which is why we have the House Rule to always have a clustered index ...) |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-11-09 : 15:17:20
|
| I believe it does matter if there is foreign key reference to one of the columns of this 50 row table. Let's say you have another table with million rows that joins to this table and with no index, you bet your bottom it will affect performance. the answer always is it depends !If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
index