Question: How to Find Cardinality or Uniqueness for any Column?
Answer: The question was actually came up during my training SQL Server Performance Tuning Practical Workshop, while I was explaining various concepts related to cardinality estimation, compatibility level and its impact on SQL Server’s performance.
Though the question is very simple, it is very valid as well. It is not possible that every single person knows the meaning of cardinality.
Let us see a very simple script which gives us cardinality in percentage from the AdventureWorks database table SalesOrderDetail and column SalesOrderID.
SELECT COUNT(DISTINCT SalesOrderID)*100.0/Count(*) 'Distinct_SalesOrderID (in %)' FROM [AdventureWorks2014].[Sales].[SalesOrderDetail]
If you see the resultsets, it shows how many percentages of unique values are there for the column SalesOrderID details in the table.
It is very important to understand the uniqueness as well as the datatype of the column and how it is used in SQL Server. They play very important and crucial role in creating indexes as well as building execution plan, however, I have often noticed that many performance tuning experts either do not know about it or do not talk about it during their training.
If you sign up for my SQL Server Performance Tuning Practical Workshop, I make sure that I explain you in detail with a real-world example.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
