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