What do you understand by Column Density in SQL Server?

Density in SQL Server: Density, when used to describe the data in a column, is a measure of how often duplicate values occur in that colum...

Density in SQL Server:
Density, when used to describe the data in a column, is a measure of how often duplicate values occur in that column. Another way to think of density is as a measure of the uniqueness of the data in a column: high density –> less unique data. Density values range from 0 to 1.0.  There are different (but equivalent) ways to think of density.

Density = 1/[# of distinct values in a column]
Density = Avg. number of duplicates for a given value / Total row count

Consider a Customers table with a CountryID column and a RegionID column. Suppose that 100 different CountryID values are present in the table, and 1000 different RegionIDs are present. The density of the CountryID column would be higher than the density of the RegionID column because a typical country is less unique than a typical Region. Note that this is an overall characterization of a range of values. There may be some regions that occur more frequently than some countries — a U.S.-based company might have more customers in California than in Estonia, for example.  But CountryID still has higher density than RegionID simply because the average country is represented in more rows than the average region.

Density of the CountryID column:

Density = 1/[# of distinct values in the column]
Density = 1/100 = 0.01

Note that the density of 0.01, or 1%, corresponds to the percentage of the total rows that would be returned by a query for a single value.  In other words, an average country makes up 1% of the table.  if there were 10,000 rows in the table, there were be 100 rows (1% of 10,000) per country, on average.

Density of the RegionID column:

Density = 1/[# of distinct values in a column]
Density = 1/1000 = 0.001

The density of the CountryID column (0.01) is higher than the density of the RegionID column (0.001) because the CountryID column is less unique.

Click here for read complete msdn post

Related

SQL SERVER PERFORMANCE TUNING INTERVIEW QUESTIONS 3777845240373849319

Post a comment

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item