Monday, November 24, 2008

Average Size of Data In A Column

A script to return the average size of the values in a column. It also provides a simple histogram of the data distribution.

SELECT AVG(DATALENGTH(MyColumn)) AS 'Avg Size'
  FROM MyDB.dbo.MyTable

SELECT AVG(DATALENGTH(MyColumn))           AS 'Avg Size',
       FLOOR(LOG10(DATALENGTH(MyColumn)))  AS 'Log',
       COUNT(*)                            AS 'Count in Log' 
  FROM MyDB.dbo.MyTable
 GROUP BY FLOOR(LOG10(DATALENGTH(MyColumn)))
 ORDER BY FLOOR(LOG10(DATALENGTH(MyColumn))) DESC

0 comments:

Post a Comment