![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
Column selectivity (SQL Server 2k8) |
|
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).
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
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
|
|
|