May 2, 2011 at 11:42 pm
Our lookup table contains only 10 records.
But the transaction table, which has referenced the lookup table, have 20,00,000 records.
We want to create index on the lookup table.
Which one is best - creating clustered index or non-clustered index.
May 2, 2011 at 11:57 pm
All tables should have a clustered index (with a few specific exceptions)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2011 at 9:56 am
It is doubtful that an index on a table with 10 rows will help anything. Focus your efforts on the 20M row table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 4, 2011 at 9:59 am
Actually a FK might actually help, but I can't confirm without seeing the queries!
May 12, 2011 at 11:13 am
GilaMonster (5/2/2011)
All tables should have a clustered index (with a few specific exceptions)
Not to hijack the thread or anything...but what is your list of specific exceptions?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 12, 2011 at 11:23 am
opc.three (5/12/2011)
GilaMonster (5/2/2011)
All tables should have a clustered index (with a few specific exceptions)Not to hijack the thread or anything...but what is your list of specific exceptions?
I forgot the exact situation but it was a story from Brian Kelly IIRC.
Bottom line was that there was no natural key or any index that made sense for range scan. There was also a problem that any clustered index slowed down inserts / updates with no benefits select side.
I don't have anymore info, sorry.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply