Introduction

The Fill Rate is one of the most important metric in many of the business related decisions. So Knowing Fill Rate is one of the essential tasks in Data Analysis and Decision making in business processes. In this article, we're going to explain how to find the Fill Rate of a Table using T-SQL Queries.



Scenario

Observing Fill Rate is the key factor for knowing and improving data quality that creates the fulfillment to both our management as well as our end users. Need to find Fill Rate of a Table, How to do it?

What is meant by Fill Rate?

The Fill Rate is defined as the number of filled entries at each Column level that is divided by the total number of rows in a table. Depends on the total number of rows that is present in a table, we can figure out the Fill Rate easily.

Challenges

The Schema changes like below things makes our Fill Rate approach little difficult than actual.

  • Table name changes
  • Column name changes
  • Data type changes
  • Removing Existing columns
  • Adding New Columns

Due to the above challenges, we cannot simply go for Static Solution to find Fill Rate of a table. Instead, we need something like Dynamic Approach to avoid our future re-works.

Prerequisite

In the below sample, we are going to use one stored procedure named 'Get_FillRate' for demo. If any one have the same object name in database, please make sure to change the below stored procedure name.

Sample Table Creation with Data Loading Script


--dropping temp table if exists
IF OBJECT_ID('TempDb..#TestEmp') IS NOT NULL
    DROP TABLE #TestEmp;
  
CREATE TABLE #TestEmp
(
    [TestEmp_Key] INT IDENTITY(1,1) NOT NULL,
    [EmpName] VARCHAR(100) NOT NULL,
    [Age] INT NULL,
    [Address] VARCHAR(100) NULL,
    [PhoneNo] VARCHAR(11) NULL,
    [Inserted_dte] DATETIME NOT NULL,
    [Updated_dte] DATETIME NULL,
 CONSTRAINT [PK_TestEmp] PRIMARY KEY CLUSTERED
 (
    TestEmp_Key ASC
 )
);
GO
   
INSERT INTO #TestEmp
(EmpName,Age,[Address],PhoneNo,Inserted_dte)
VALUES
('Arul',24,'xxxyyy','1234567890',GETDATE()),
('Gokul',22,'zzzyyy',NULL,GETDATE()),
('Krishna',24,'aaa','',GETDATE()),
('Adarsh',25,'bbb','1234567890',GETDATE()),
('Mani',21,'',NULL,GETDATE()),
('Alveena',20,'ddd',NULL,GETDATE()),
('Janani',30,'eee','',GETDATE()),
('Vino',26,NULL,'1234567890',GETDATE()),
('Madhi',25,'ggg',NULL,GETDATE()),
('Ronen',25,'ooo',NULL,GETDATE()),
('Visakh',25,'www',NULL,GETDATE()),
('Jayendran',NULL,NULL,NULL,GETDATE());
GO
   
SELECT [TestEmp_Key],[EmpName],[Age],[Address],[PhoneNo],[Inserted_dte],[Updated_dte] FROM #TestEmp;
GO


Temp Table - #TestEmp



SQL Procedure For Finding Fill Rate in a Table - Dynamic Approach

Input Parameters

Both of the Input Parameters are mandatory.

1. @p_TableName - Data type used for this input Parameter is NVARCHAR(128) and Nullability is NOT NULL.

2. @p_Include_BlankAsNotFilled - Data type used for this input Parameter is BIT and Nullability is NOT NULL and either 0 or 1 needs to give. 0 is by Default and 0 means OFF. 1 is ON (when given as 1 - Blank entries will be considered As Not Filled Data).

Output Columns

There are Two output Columns. both of those are Non Nullable Output Columns.

1. [Column Name] - Data type used for this Output Column is sysname and Nullability is NOT NULL. All the Column Names for the user given Table Name would come as row values.

2. [Fill Rate (%)] -  Data type used for this Output Column is DECIMAL(5,2) and Nullability is NOT NULL. Values from 0.00 to 100.00 would come in result with respective Column Names.

Info reg Stored Procedure

  •   Created the store Procedure named - 'Get_FillRate'.
  •   To avoid the number of rows returned, set NOCOUNT as ON.
  •   Try, Catch Blocks are added for error handling's. 
  •   To read Uncommitted Modifications, set TRANSACTION ISOLATION LEVEL as READ UNCOMMITTED.
  •   Parameter Sniffing Concept is also included.
  •   Some handling's done on the Table Name input parameters to support user typing table name formats like '.table_name','..table_name','...table_name','table_name','[table_name]','dbo.table_name','dbo.[table_name]','[dbo].[table_name]' etc.,
  •   Validation is included at the start, when user gives other than 'table name', stored procedure would throw 'Table not exists in this Database' as error message.
  •   System table named SYS.OBJECTS and SYS.COLUMNS and System View named INFORMATION_SCHEMA.COLUMNS are used inside the stored procedure.
  •   ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS is used, to return the result set with the same column order that the table structure already has.
  •   COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS is used, to support conditions like blank is either need to consider or not, as not filled entries.
  •   COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS is used, to show the final result set with respective fill rates.
  •   Dynamic Query is used, to support dynamic approach and this would avoid all the challenges that would come in static solutions like schema changes.
  •   Both Method 1(Dynamic Query with WHILE LOOP) and Method 2(Dynamic Query with UNION ALL) produces same result sets and carries same functionality where some metrics like CPU time,Elapsed Time,Logical reads that are better in Method 2.

Method 1 - With the use of WHILE Loop


/******************************************************************************************************/
-- Created By : TECHNET WIKI MEMBER
-- Version    : 1.0
-- Created On : 20/05/2020
-- Description: --To Fetch the List of all columns of a table with their fill rates in %.
/******************************************************************************************************/
CREATE OR ALTER PROCEDURE [dbo].[Get_FillRate]  
(
    @p_TableName                  NVARCHAR(128),
    @p_Include_BlankAsNotFilled   BIT = 0 -- 0-OFF(Default); 1-ON(Blank As Not Filled Data)
)
AS
BEGIN
  
BEGIN TRY
   
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
--Parameter Sniffing
DECLARE @TableName                  NVARCHAR(128),
        @Include_BlankAsNotFilled   BIT,
        @ColumnName                 NVARCHAR(128),
        @R_NO                       INT,
        @DataType_Field             BIT,
        @i                          INT, --Iteration
        @RESULT                     NVARCHAR(MAX);
  
SELECT  @TableName                  =   @p_TableName,
        @Include_BlankAsNotFilled   =   @p_Include_BlankAsNotFilled,
        @i                          =   1;
          
--To Support some of the table formats that user typing.
SELECT @TableName   =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@TableName,'[',''),']',''),'dbo.',''),'...',''),'..',''),'.','');       
   
--validation
IF NOT EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE [TYPE]='U' AND [NAME]=@TableName )   
    BEGIN
        SELECT Result = 1 , Reason ='Table not exists in this Database' ;
        RETURN 1;
    END;
   
--dropping temp table if exists - for debugging purpose
IF OBJECT_ID('TempDb..#Temp') IS NOT NULL
    DROP TABLE #Temp;
IF OBJECT_ID('TempDb..#Columns') IS NOT NULL
    DROP TABLE #Columns;
  
--temp table creations
CREATE TABLE #Temp
(
[R_NO] INT NOT NULL,
[ColumnName] NVARCHAR(128) NOT NULL,
[FillRate] DECIMAL(5,2) NOT NULL
PRIMARY KEY CLUSTERED (ColumnName)
);
  
CREATE TABLE #Columns
(
[R_NO] INT NOT NULL,
[Name] [sysname] NOT NULL,
[DataType_Field] BIT NOT NULL
PRIMARY KEY CLUSTERED ([Name])
);
   
INSERT INTO #Columns ([R_NO],[Name],[DataType_Field])
SELECT
    COLUMN_ID,
    [Name],
    IIF(collation_name IS NULL,0,1)
FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(@TableName);
   
WHILE @i <= ( SELECT MAX(R_NO) FROM #Columns) --Checking of Iteration till total number of columns
    BEGIN
        SELECT @DataType_Field=DataType_Field,@ColumnName=[Name],@R_NO=[R_NO] FROM #Columns WHERE R_NO = @i;
   
          SET @RESULT =
            'INSERT INTO #Temp ([R_NO],[ColumnName], [FillRate]) ' +
            'SELECT ' + QUOTENAME(@R_NO,CHAR(39)) + ',
                ''' + @ColumnName + ''',
                CAST((100*(SUM(
                    CASE WHEN ' +
                        CASE
                            WHEN @Include_BlankAsNotFilled = 0
                              THEN '[' + @ColumnName + '] IS NOT NULL'
                            WHEN @DataType_Field = 0
                              THEN '[' + @ColumnName + '] IS NOT NULL'
                            ELSE 'ISNULL([' + @ColumnName + '],'''')<>'''' ' END +
                    ' THEN 1 ELSE 0 END)*1.0 / COUNT(*)))
                AS DECIMAL(5,2))
            FROM ' + @TableName;
   
        --PRINT(@RESULT); --for debug purpose
        EXEC(@RESULT);
   
        SET @i += 1; -- Incrementing Iteration Count
    END;
   
 --Final Result Set
    SELECT
      ColumnName AS [Column Name],
      FillRate AS [Fill Rate (%)]
    FROM #TEMP
    ORDER BY [R_NO];
   
    RETURN 0;
  
END TRY
BEGIN CATCH  --error handling even it is fetching stored procedure
    SELECT
         ERROR_NUMBER()     AS ErrorNumber
        ,ERROR_SEVERITY()   AS ErrorSeverity
        ,ERROR_STATE()      AS ErrorState
        ,ERROR_PROCEDURE()  AS ErrorProcedure
        ,ERROR_LINE()       AS ErrorLine
        ,ERROR_MESSAGE()    AS ErrorMessage;
    RETURN 1;
END CATCH;
  
END;

Execute this stored procedure - Method 1 by passing the table name like below

Execute like below if we need to consider NULL values alone as not filled

EXEC [Get_FillRate] @p_TableName='#TestEmp',@p_Include_BlankAsNotFilled=0;

Execute like below if we need to consider both NULL values and empty/blank values as not filled

EXEC [Get_FillRate] @p_TableName='#TestEmp',@p_Include_BlankAsNotFilled=1;

Method 1 -Output



Method 2 - With the use of UNION ALL


/******************************************************************************************************/
-- Created By : TECHNET WIKI MEMBER
-- Version    : 2.0
-- Created On : 20/05/2020
-- Description: - Version 1.0 - To Fetch the List of all columns of a table with their fill rates in %.
-- 20/05/2020 - Version 2.0 -  TECHNET WIKI MEMBER - Made Changes by avoiding while loop.
/******************************************************************************************************/
   
CREATE OR ALTER PROCEDURE [dbo].[Get_FillRate]  
(
    @p_TableName                  NVARCHAR(128),
    @p_Include_BlankAsNotFilled   BIT = 0 -- 0-OFF(Default); 1-ON(Blank As Not Filled Data)
)
AS
BEGIN
  
BEGIN TRY
   
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
--Parameter Sniffing
DECLARE @TableName                  NVARCHAR(128),
        @Include_BlankAsNotFilled   BIT,
        @RESULT                     NVARCHAR(MAX);
  
SELECT  @TableName                  =   @p_TableName,
        @Include_BlankAsNotFilled   =   @p_Include_BlankAsNotFilled,
        @RESULT                     =   '';
          
--To Support some of the table formats that user typing.
SELECT @TableName   =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@TableName,'[',''),']',''),'dbo.',''),'...',''),'..',''),'.','');               
   
--validation
IF NOT EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE [TYPE]='U' AND [NAME]=@TableName )   
    BEGIN
        SELECT Result = 1 , Reason ='Table not exists in this Database' ;
        RETURN 1;
    END;
  
--dropping temp table if exists - for debugging purpose
IF OBJECT_ID('TempDb..#Columns') IS NOT NULL
    DROP TABLE #Columns;
      
--temp table creations
CREATE TABLE #Columns
(
[ORDINAL_POSITION] INT NOT NULL,
[COLUMN_NAME] [sysname] NOT NULL,
[DataType_Field] BIT NOT NULL,
[TABLE_NAME] [sysname] NOT NULL
PRIMARY KEY CLUSTERED ([ORDINAL_POSITION],[COLUMN_NAME])
);
  
INSERT INTO #Columns ([ORDINAL_POSITION],[COLUMN_NAME],[DataType_Field],[TABLE_NAME])
SELECT
    [ORDINAL_POSITION],
    [COLUMN_NAME],
    CASE WHEN COLLATION_NAME IS NOT NULL THEN 1 ELSE 0 END,
    [TABLE_NAME]
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@tablename; --Using System_View
   
--Final Result Set
SELECT @RESULT = @RESULT+ N'SELECT '''+C.COLUMN_NAME+''' AS [Column Name],
                            CAST((100*(SUM(
                                    CASE WHEN ' +
                                        CASE
                                            WHEN @include_blankasnotfilled = 0
                                              THEN '[' + C.COLUMN_NAME + '] IS NOT NULL'
                                            WHEN C.[DataType_Field]=0
                                              THEN '[' + C.COLUMN_NAME + '] IS NOT NULL'
                                            ELSE 'ISNULL([' + C.COLUMN_NAME + '],'''')<>'''' ' END +
                                    ' THEN 1 ELSE 0 END)*1.0 / COUNT(*)))
                            AS DECIMAL(5,2)) AS [Fill Rate (%)]
                        FROM '+C.TABLE_NAME+' UNION ALL '
FROM #Columns C;
  
SET @RESULT=LEFT(@RESULT,LEN(@RESULT)-10); --To Omit 'Last UNION ALL '.
  
--PRINT(@RESULT); --for debug purpose
EXEC(@RESULT);
   
    RETURN 0;
  
END TRY
BEGIN CATCH  --error handling even it is fetching stored procedure
    SELECT
         ERROR_NUMBER()     AS ErrorNumber
        ,ERROR_SEVERITY()   AS ErrorSeverity
        ,ERROR_STATE()      AS ErrorState
        ,ERROR_PROCEDURE()  AS ErrorProcedure
        ,ERROR_LINE()       AS ErrorLine
        ,ERROR_MESSAGE()    AS ErrorMessage;
    RETURN 1;
END CATCH;
  
END;

Execute this stored procedure - Method 2 by passing the table name like below

Execute like below if we need to consider NULL values alone as not filled

EXEC [Get_FillRate] @p_TableName='#TestEmp',@p_Include_BlankAsNotFilled=0;

Execute like below if we need to consider both NULL values and empty/blank values as not filled

EXEC [Get_FillRate] @p_TableName='#TestEmp',@p_Include_BlankAsNotFilled=1;


Method 2 -Output


Metrics Difference between Method 1 Vs Method 2

The below four metrics taken for consideration for knowing the difference between Method 1 Vs Method 2

  • No. of Query Sets in Exec Query Plan

  • Total CPU Time (in ms)

  • Total Elapsed Time (in ms)

  • Total Logical Reads



Applies To

  • SQL Server
  • Data Analysis

Conclusion

In conclusion, we have seen how to find the Fill Rate of a Table Using T-SQL Queries that is applicable to run in both AZURE and On-Premises SQL Databases. Thus, it would helps us to take business decisions effectively as well as immediately.

Future Scope

As a future scope, We can do some more enhancements (some of the ideas mentioned below) with this and can do many more things like supporting Fill Rates for
  • Required column list of a table in a database (using exclude/include option and column_list as input parameter)
  • Required column list in a database (by introducing separate input parameter for column name and supporting null in table name)
  • Required list of tables in a database (by changing the data length in input parameter)
  • All tables in a database (by removing the table name filter)

Reference

See Also