Basic Logics · Data Analysis · Data Management · Data Migrations · Real Scenario Logic's

To Find Fill Rate in a Table on both AZURE and On-Premise – Key Metric in Data Analysis

Introduction

Knowing Fill Rate is one of the essential tasks in Data Analysis/Management and Migration Processes. It helps us to take Business Decisions effectively.

In this post, will see how to find the Fill rate in a table using SQL Queries that is Applicable for both AZURE and On-Premises…

What is meant by Fill Rate?

The sum of the filled entries at each column level divided by the total number of rows in the table.

Depends on the total number of rows of a table,we can figure out the Fill Rate easily.

Sample Table Creation/Data Loading Scripts

CREATE TABLE dbo.[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 dbo.[TestEmp]
(EmpName,Age,[Address],PhoneNo,Inserted_dte)
VALUES
('Arul',24,'xxxyyy','1234567890',GETDATE()),
('Kiran',22,'zzzyyy',NULL,GETDATE()),
('Karan',24,'aaa','',GETDATE()),
('Abhi',25,'bbb','1234567890',GETDATE()),
('Mano',21,'',NULL,GETDATE()),
('Kaushik',20,'ddd',NULL,GETDATE()),
('Rahmoun',30,'eee','',GETDATE()),
('Subh',26,NULL,'1234567890',GETDATE()),
('Sachin',25,'ggg',NULL,GETDATE()),
('Jay',25,'ooo',NULL,GETDATE()),
('Neeraj',25,'www',NULL,GETDATE()),
('Mustafa',NULL,NULL,NULL,GETDATE());
GO

SELECT * FROM dbo.[TestEmp];  
GO
TestEmp Table with Data

SQL Script For Finding Fill Rate in a Table

The below scripts would helps us to find the Fill rate in a Table (columns dynamically handled Using Dynamic Query). There is a filter for Setting ON/OFF for Including Blank as Not Filled Data (Default-OFF). WHILE Loop is used for retrieving all columns of a user given table.

/******************************************************************************************************/
-- Created By : Arulmouzhi Ezhilarasan
-- Version    : 1
-- Created On : 19/11/2019
-- Description: --To Fetch the List of all columns of a table with their fill rates in %.
/******************************************************************************************************/

CREATE OR ALTER PROCEDURE [dbo].[GetFillRate]    --	EXEC [GetFillRate] 'TestEmp'
(
	@TableName					NVARCHAR(128),
	@Include_BlankAsNotFilled	BIT = 1 -- 0-OFF(Default); 1-ON(Blank As Not Filled Data)
)
AS
BEGIN

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;   

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;

DECLARE @ColumnName NVARCHAR(128),@DataType_Field BIT,@i INT,@RESULT NVARCHAR(MAX);

SET @i=1;

CREATE TABLE #Temp( TableName NVARCHAR(128) NOT NULL,ColumnName NVARCHAR(128) NOT NULL,FillRate DECIMAL(5,2) NOT NULL);

SELECT c.[Name],IIF(collation_name IS NULL,0,1) AS DataType_Field,ROW_NUMBER() OVER (ORDER BY COLUMN_ID) AS R_NO INTO #Columns
FROM SYS.COLUMNS c WHERE c.OBJECT_ID = OBJECT_ID(@TableName);

WHILE @i <= ( SELECT ISNULL(MAX(R_NO),0) FROM #Columns)
	BEGIN 
		SET @ColumnName = ( SELECT [Name] FROM #Columns WHERE R_NO = @i);
		SET @DataType_Field = ( SELECT DataType_Field FROM #Columns WHERE R_NO = @i);

		  SET @RESULT = 
			'INSERT INTO #Temp (TableName, ColumnName, FillRate) ' + 
			'SELECT ''' + REPLACE(REPLACE(REPLACE(@TableName,'dbo.',''),'[',''),']','') + 
				'''' + ',''' + @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);
		EXEC(@RESULT);

		SET @i += 1;
	END;

SELECT 
  ColumnName AS [Column Name], 
  FillRate AS [Fill Rate (%) ] 
FROM #TEMP;

	RETURN 0;
END;
Fill Rate of each columns in a Table

Conclusion

The Fill rate is one of the important metric in most business related decisions. It is the key factor for knowing and improving data quality that creates the fulfillment to both the clients and the end customers.

Thanks for Reading!
Advertisement

4 thoughts on “To Find Fill Rate in a Table on both AZURE and On-Premise – Key Metric in Data Analysis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s