SQL Server Performance

Selectivity of a column

Discussion in 'T-SQL Performance Tuning for Developers' started by arb, Dec 3, 2002.

  1. arb New Member

    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


  2. bradmcgehee New Member

    Thanks for your contribution.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page