T-SQL : Finding Fill Rate of Table

T-SQL : Finding Fill Rate of Table

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.

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.

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.

analytics data sql azure

What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server

In this article, you learn how to set up Azure Data Sync services. In addition, you will also learn how to create and set up a data sync group between Azure SQL database and on-premises SQL Server.

How to access an Azure SQL database from Azure Data Lake Analytics

In this article, we will learn to access data in an Azure SQL database from Azure Data Lake Analytics.

Building U-SQL jobs locally for Azure Data Lake Analytics

In this tutorial, we will learn how to develop U-SQL jobs locally, which once ready, can be deployed on the Azure Data Lake Analytics service on the Azure cloud.

Your Data Architecture: Simple Best Practices for Your Data Strategy

Your Data Architecture: Simple Best Practices for Your Data Strategy. Don't miss this helpful article.