I have a few tables with 4 to 10 rows. We don't anticipate that these tables will ever grow much more that a few more rows.
Does it make sense to put an index on their primary keys.
+11
A:
If they have primary keys as you stated then you already have at least one index. This probably is a clustered index, and I thing you are good to go.
Otávio Décio
2009-06-11 13:13:54
+2
A:
You should almost always have a clustered index in the very least. I would say yes, go ahead and index them.
It certainly won't hurt, and should the data in that table grow past your expectations, you will at least have a simple indexing strategy in place to help mitigate the effect of the increased table size.
Aaron Alton
2009-06-11 13:14:55
what fill factor would you use for a clustered index on a table like this?
KM
2009-06-11 13:16:16
Fill factor really depends on the nature of the inserts and updates.For instance, if you tend to insert records in the order defined by your clustered index, and if you perform few or no updates, a high fill-factor is best (even 100%, if you have no updates). In that way, you're scanning a few data pages as possible for any given fetch.For a table with out-of-order inserts, or any more than sporadic updates, a lower fill factor is warranted.
Aaron Alton
2009-06-11 13:53:10
What if the values you want index change often? Wouldn't clustering decrease performance?
johnnycrash
2009-06-12 21:36:36
+1
A:
If your using a Primary Key then you will already have one clustered index.
kevchadders
2009-06-11 13:16:58
+1
A:
Hopefully you have a primary key which is your Clustered index. But other than column(s) in your index, a 4 to 10 row table is tiny - there is more cost associated with looking up an index than an actual table scan.
Someone please keep me honest here - for SQL 2008 in large scale production and reporting environments, we do not bother with indices on tables with less than 50k rows.
Raj More
2009-06-11 14:02:25
+6
A:
The correct answer is: IT DOESN'T MATTER.
Any time the table is small enough to fit inside a single 8k data page, SQL server can simply load that one page, and have the "entire table" available to do whatever it needs.
A clustered index is the table itself, so if you add a clustered index, you're not really adding any overhead, you're just specifying a sort preference within the single data page where the table resides.
A nonclustered index, on the other hand, is a separate object, so it would just be wasted space, because it would never be used. (The query optimizer is never going to load an index that only has pointers to a single data page. It'll just load the only data page directly).
By all means make sure you have a primary key, but if you also add the clustered index, it isn't going to mean much (and likely wouldn't ever be used) unless the table grows well beyond one page.
BradC
2009-06-11 14:28:06