When you are tuning queries, there are two fundamental concepts that you need to be well aware of – Density & Selectivity. In one of my previous notes, I talked about Selectivity. Today, let’s talk about Density. In simple words, it measures the uniqueness of data in a column. Note that we are talking about per-column basis. For example, a column like EmailAddress will have more unique data compared to, say, Country. So it is about the uniqueness of the row taking into account the data present in the column. From the perspective of EmailAddress, your data is very unique, but from the perspective of Country, the data is less unique. High density means less unique data and low density means high unique data. Density ranges from 0 to 1.0. The formula is Density = 1/(Number of distinct values in a column). So what is this used for? Well, this computation helps in cardinality estimation. The cardinality estimator is a very complicated piece of code inside the SQL query optimizer that leverages the density factor for its computation. The density is pre-calculated and stored as part of statistics. Remember, the stats object is also based on column (column data). Sounds interesting? Watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.