SQL Server Performance Forum – Threads Archive

Selectivity of a column

You can make the code to calculate the selectivity of a column (from the latest newsletter) more useful by creating a stored procedure. Then it becomes a simple matter to call:
exec spFindSelectivity ‘tblXXX’, ‘colYYY’ CREATE PROCEDURE spFindSelectivity @Table VARCHAR(100), @Column VARCHAR(100) AS –Finds the Degree of Selectivity for a Specific Column in a Row
Declare @total_unique float
Declare @total_rows float
Declare @selectivity_ratio float declare @SQL varchar(255) SELECT @total_unique = 0
SELECT @total_rows = 0
SELECT @selectivity_ratio = 0 create table #tmpUNique (cnt int)
create table #tmptotal (cnt int) –Finds the Total Number of Unique Rows in a Table
–Be sure to replace OrderID below with the name of your column
–Be sure to replace [Order Details] below with your table name set @SQL = ‘SELECT COUNT(DISTINCT ‘ + @column + ‘) AS cnt FROM ‘ + @table
insert into #tmpUnique
exec(@SQL) SELECT @total_unique = cnt from #tmpUnique –Calculates Total Number of Rows in Table
–Be sure to replace [Order Details] below with your table name set @SQL = ‘SELECT COUNT(*) AS cnt FROM ‘ + @table
insert into #tmpTotal exec(@SQL) SELECT @total_rows = cnt from #tmpTotal –Calculates Selectivity Ratio for a Specific Column
SELECT @selectivity_ratio = ROUND((SELECT
@total_unique/@total_rows),2,2)
SELECT @selectivity_ratio as ‘Selectivity Ratio’ drop table #tmptotal
drop table #tmpunique

---

Thanks for your contribution.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

]]>