August 29, 2009 at 8:48 am
I have created script to find selectivity of each columns for every tables. In those some tables with less than 100 rows but selectivity of column is more than 50%. where Selectivity = Distinct Values / Total Number Rows
So, is those column are eligible for index?
Or, can you tell, how much minimum rows require for eligibility for creating index?
August 29, 2009 at 9:15 am
Paresh Prajapati (8/29/2009)
So, is those column are eligible for index?Or, can you tell, how much minimum rows require for eligibility for creating index?
They're certainly eligible for indexes, nothing's stopping you from creating an index on a column with low selectivity. Whether it's useful to SQL or not is a completely different question. If you're planning on single column indexes, they probably won't be useful. Multi-column indexes might be useful, but that depends completely on what queries are executed against that table.
For more info, see http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/ and http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
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
August 29, 2009 at 1:39 pm
Read Gail's entries, and then test your queries. Look at the execution plans and see if the indexes are used.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply