SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Column selectivity (SQL Server 2k8)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Column selectivity (SQL Server 2k8)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post Column selectivity (SQL Server 2k8) Reply with quote
I use this snippet frequently when I have to implement indices for new queries on production servers. It helps decide index column order (wherever it is a question, of course).

Code:

DECLARE @total_unique FLOAT = 0
DECLARE @total_rows FLOAT = 0

$$DECLARE @targy VARCHAR(256) = '$OBJECT$', @sema VARCHAR(256), @tabla VARCHAR(256), @mezo VARCHAR(256)
IF PARSENAME(@targy,3) IS NULL SELECT @sema = PARSENAME(@targy,2), @tabla = PARSENAME(@targy,1) ELSE SELECT @sema = PARSENAME(@targy,3), @tabla = PARSENAME(@targy,2), @mezo = PARSENAME(@targy,1)
DECLARE @sql VARCHAR(max)

SELECT @sql = '
SELECT @total_unique = (
          SELECT COUNT(1)
          FROM   (
                    SELECT DISTINCT
                       t' + ISNULL('.' + @mezo,'') + '
                    FROM ' + @sema + '.' + @tabla + ' AS t
                 ) AS a
       )

SELECT @total_rows = (
          SELECT COUNT(*)
          FROM ' + @sema + '.' + @tabla + ' AS t
       )
-- selectivity ratio for a specific column
SELECT ROUND(@total_unique/@total_rows,2,2) AS ''Selectivity ratio'', @total_unique AS ''Total unique'', @total_rows AS ''Total rows'' '

SELECT @sql
$$

Thu Jun 30, 2011 10:33 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.