In following from my previous post, and in the interests of a free shirt from graz, I present another metric designed to summarise the "goodness" of a physical database schema

The Potentially Useless Column (PUC) Index analysis the number of NULL defined columns versus the total number of columns. 

The PUC Index has 3 indicators (TotalColumns, NULLColumns and Index) and applies to individual Tables and the Database

Interpreting the results is very straight foward.  Any result in either the Table or Database with a PUC Index over 0.00 must be investigated and justified. 

If I had to give a general guidline..... Any database that scores more than 0.05 (5% of the columns are PU)  needs further considerations and any database that scores more the 0.5 (50%)  is probably an EXCEL spreadsheet.

-- Table
Select TABLE_NAME, TotalColumns, NULLColumns
    , cast(NULLColumns as money)/ TotalColumns  as PUCIndex
from
(
    select T.TABLE_NAME , count(*) as TotalColumns
        , sum(case when IS_NULLABLE = 'YES' then 1 else 0 end) as NULLColumns
    from INFORMATION_SCHEMA.COLUMNS C
    inner join INFORMATION_SCHEMA.TABLES T on T.TABLE_NAME = C.TABLE_NAME
    where T.TABLE_TYPE = 'BASE TABLE' 
    group by T.TABLE_NAME
) as x

-- Database
Select sum(TotalColumns) as TotalColumns
    ,  sum(NULLColumns) as TotalPUCs
    , cast(sum(NULLColumns) as money)/ sum(TotalColumns)  as PUCIndex
    
from 
(
Select TABLE_NAME, TotalColumns, NULLColumns
    , cast(NULLColumns as money)/ TotalColumns  as PUCIndex
from
(
    select T.TABLE_NAME , count(*) as TotalColumns
        , sum(case when IS_NULLABLE = 'YES' then 1 else 0 end) as NULLColumns
    from INFORMATION_SCHEMA.COLUMNS C
    inner join INFORMATION_SCHEMA.TABLES T on T.TABLE_NAME = C.TABLE_NAME
    where T.TABLE_TYPE = 'BASE TABLE' 
    group by T.TABLE_NAME
) as x
) as y