Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 The Yak Corral
 Size does matter!

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-12 : 02:56:59
I was having a little design rant with Rob about an ERP system I'm working with (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188112) and I created this query to count the "clustered index column distribution". What it basically does is count the number of columns in the clustered index and then count the number of tables that has the same count.

The following result means that there are 32 tables in the database (out of 3327 tables) that have 16 columns in the clustered index, and the percentage means that 0.96% of the tables have 16 columns in their clustered indexes

CAN ANYBODY BEAT THIS???
ColumnsInClusteredIndex	TablesCount	TotalCount	Percentage
16 32 3327 0.96
15 15 3327 0.45
14 23 3327 0.69
13 33 3327 0.99
12 38 3327 1.14
11 66 3327 1.98
10 69 3327 2.07
9 105 3327 3.16
8 165 3327 4.96
7 245 3327 7.36
6 322 3327 9.68
5 458 3327 13.77
4 535 3327 16.08
3 631 3327 18.97
2 407 3327 12.23
1 183 3327 5.50


Here is the query:
with cte as (
select distinct
TableName = OBJECT_NAME(ic.OBJECT_ID),
ColumnName = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.index_id = 1
),
cte2 as (
select
TableName,
ColumnsInClusteredIndex = count(*)
from cte
group by TableName
),
cte3 as (
select TotalCount = count(*) from cte2)

select
ColumnsInClusteredIndex,
TablesCount = count(*),
TotalCount,
Percentage = CONVERT(decimal(5, 2), ((count(*)*1.0) / TotalCount) * 100)
from cte2
cross join cte3
group by ColumnsInClusteredIndex, TotalCount
order by ColumnsInClusteredIndex desc


- Lumbago
My blog-> http://thefirstsql.com

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-12 : 07:45:15
Not sure why you posted here but it is interesting to me (a SQL Server DBA).

djj
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-09-12 : 11:21:02
Mine is rather mundane by comparison...


ColumnsInClusteredIndex TablesCount TotalCount Percentage
6 1 914 0.11
5 5 914 0.55
4 6 914 0.66
3 34 914 3.72
2 183 914 20.02
1 685 914 74.95










How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page
   

- Advertisement -