February 18, 2015 at 3:51 am
Hi There,
Why should we consider selectivity of a table to create index?
Which is best selectivity value to create an index ?
thanks in advance
February 18, 2015 at 4:56 am
Selectivity of the columns that make up the index key, not of the table itself. So if your index key only has a small number of distinct values, it's not likely to be used much, if at all, since the query optimizer will deem that a table or clustered index scan is more efficient. Unless it's a covering index, of course. You'll only know what selectivity is good by testing for your data and your workload.
John
February 18, 2015 at 5:48 am
Selectivity is how unique any given value on a column or set of columns will be. This information is stored with the statistics for the index you create. The most important part of the statistics for the query optimizer that helps it the most in making a determination on whether or not to use a given index is the histogram. The histogram is only ever created on the first column of an index. So, in order for the statistics to be interesting, they need to be very unique so that they're interesting to the optimizer and, that first column generally has to be the most unique because it creates the histogram.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2015 at 7:32 am
Grant pretty much explained it already, and very well by the way.
But in other words ... if your column or Index is not selective enough, there is a chance that SQL engine will not pick your Index or will do a scan instead.
Imagine a column where you keep the 1st name of customers. Most likely, you will have a lot of "John", "Mary", etc, it won't be selective enough. Now imagine you put an Index there. Now, if you have many many rows, any Index you may have on that column will be useless, because is not selective enough. It won't be pick or you will see an expensive scan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply