SQL server

Index density and selectivity.

Two terms that used to define an index’s weight/quality are: selectivity and density. I have come across these terms many times and assumed their meaning which was mostly right. To end those assumptions and have a proper explanation, see the example below:

In a company with 1,000 employees, an index on Date of Birth would likely be highly selective—meaning that a query for employees born on a given day should, statistically, never return more than an average of 3 or 4 employees per day. In SQL Server terminology, this index would have a density of .003% (or 3 out of 1,000), which in turn, would translate to an index selectivity of .997 (index density and selectivity are inversely related or proportional.) Essentially, this index would be beneficial in any query used against the birth date of employees, as the data within this index is ‘selective’ or capable of discriminating against different types of results. Within SQL Server, the more selective an index, the greater the chance that it will get used, and the more efficient it will be at returning results in a performant manner.

Full credit for example goes to an excerpt from a whitepaper by Michael K. Campbell

TOP 10 SQL PERFORMANCE BOOSTERS
INCREASE SQL SERVER PERFORMANCE
WITH THE HARDWARE YOU ALREADY OWN
BY MICHAEL K. CAMPBELL
Advertisements
Standard

One thought on “Index density and selectivity.

  1. Waqar Arif says:

    Well and Easy explained. From my side Its 10/10 as per my brain perceive it.

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s