A Practical Guide to Implement Data Quality Rules in SQL Server

Problem

One of the most critical aspect of data quality management for data validation in any organisation is determining the data quality rules. With pre-built data governance, management and validation tools like Microsoft Purview, these rules can exist as either out-of-box or you can also add custom rules, if needed. However, when you need to create a data validation framework in a SQL environment without Microsoft Purview or other pre-built subscription-based tools, you will need to define and implement these rules yourself.

Solution

In this post, I will describe some types of business-critical data management rules that can be implemented using SQL scripting.

In a previous post Simplifying data quality validation with SQL, I demonstrated how to implement data quality management using SQL scripts from scratch. The primary focus of this post is to demonstrate how to set up these rules and implement the data quality management in SQL.

Master Catalog for business-critical data quality, management and validation rules

The list of master catalog for business-critical data quality, management and validation rules you could consider in SQL are shown below. The list has taken into account any data governance standards by grouping them into quality dimensions used in industry standards ISO 25012 and DAMA.

1. Completeness Rules – Not Null, Completeness %, Threshold Rule, Default Value Check.
2. Accuracy and Validity Rules – Range, Lookup/Domain, Reference, Regex/Pattern, Type Check, Format Validation.
3. Consistency Rules – Conditional Rules, Cross-Field Validation, Cross-Table Consistency, Referential Cardinality, Business-Specific.
4. Uniqueness and Integrity – Uniqueness, Duplicate Detection.
5. Timeliness and Trends – Timeliness, Drift/Trend Check, Outlier Detection.

Reference Table

For reference purposes, I have also created a reference table to help guide the implementation:

Now that we know what these rules are and the SQL logic/technique behind each one, lets look at how we can implement them.

STEP 1: Create SQL Server Tables

I will be creating four data tables as seen in the code below. I will also be inserting fictitious data quality issues data into the table that we will address in our examples.

-- MSSQLTips.com
-- Create Customers table
CREATE TABLE dbo.Customers (
    CustomerID INT IDENTITY PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NULL,
    PhoneNumber NVARCHAR(20) NULL,
    AnnualSpend DECIMAL(18,2) NULL,
    LastUpdatedDate DATE NULL
);
 
-- Create Products table
CREATE TABLE dbo.Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50),
    StandardCost DECIMAL(18,2),
    ListPrice DECIMAL(18,2)
);
 
-- Create Orders table
CREATE TABLE dbo.Orders (
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    ShipDate DATE,
    OrderAmount DECIMAL(18,2),
    FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES dbo.Products(ProductID)
);
 
-- Create Reference table for categories (used in lookup/reference checks)
CREATE TABLE dbo.Categories (
    CategoryID INT IDENTITY PRIMARY KEY,
    CategoryName NVARCHAR(50)
);

Next, I will insert data into the four created tables:

 -- Insert customers
INSERT INTO dbo.Customers (CustomerName, Email, PhoneNumber, AnnualSpend, LastUpdatedDate)
VALUES
('Alice', 'alice@example.com', '1234567890', 1000.50, DATEADD(DAY, -10, GETDATE())),  -- valid
('Bob', 'bob@example.com', '2345678901', 200.75, DATEADD(DAY, -40, GETDATE())),      -- LastUpdatedDate fails
('Charlie', 'charlie@example.com', '3456789012', NULL, DATEADD(DAY, -5, GETDATE())), -- AnnualSpend NULL
('David', 'david@example.com', '4567890123', 150.00, DATEADD(DAY, -50, GETDATE()));  -- LastUpdatedDate fails
 
-- Insert products
INSERT INTO dbo.Products (ProductName, Category, StandardCost, ListPrice) VALUES
('Laptop', 'Electronics', 800, 1200),       -- valid
('Desk', 'Furniture', 250, 300),            -- valid
('Chair', 'Furniture', -10, 150),           -- invalid (negative StandardCost)
('Tablet', 'Gadgets', 1500, 1800),          -- invalid (too high StandardCost)
('Fridge', 'Appliances', 600, 900);         -- valid
 
-- Insert categories
INSERT INTO dbo.Categories (CategoryName) VALUES
('Electronics'), ('Furniture'), ('Appliances');
 
-- Insert orders
INSERT INTO dbo.Orders (CustomerID, ProductID, OrderDate, ShipDate, OrderAmount) VALUES
(1, 1, '2023-11-01', '2023-11-05', 1200),
(2, 2, '2023-11-02', '2023-11-01', 300), -- ShipDate < OrderDate (invalid)
(3, 3, '2023-11-03', NULL, 150),         -- Missing ShipDate
(4, 4, '2023-11-04', '2023-11-08', -50); -- Invalid negative OrderAmount 

STEP 2: Create Validation Rules and Results

We will now create two metadata SQL tables to hold the Validation Rules and the Validation Results:

 IF OBJECT_ID('dbo.ValidationResults', 'U') IS NOT NULL
    DROP TABLE dbo.ValidationResults;
 
IF OBJECT_ID('dbo.ValidationRules', 'U') IS NOT NULL
    DROP TABLE dbo.ValidationRules;
 
-- This holds Rule metadata
CREATE TABLE dbo.ValidationRules (
    RuleID INT IDENTITY PRIMARY KEY,
    RuleName NVARCHAR(200),
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128) NULL,
    RecordKeyColumn NVARCHAR(128) NULL,
    RuleType NVARCHAR(50),
    RuleValue NVARCHAR(200) NULL,
    Severity NVARCHAR(20),
    IsActive BIT DEFAULT 1
);
 
-- This holds Results table
CREATE TABLE dbo.ValidationResults (
    ResultID INT IDENTITY PRIMARY KEY,
    RunID INT,
    RuleID INT,
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    RecordID NVARCHAR(200),
    FailedValue NVARCHAR(200),
    Severity NVARCHAR(20),
    CreatedAt DATETIME DEFAULT GETDATE()
); 

STEP 3: Create Data Quality Dimension Rules

Completeness Rules

Use the SQL script below to insert rule values into the dbo.ValidationRules table for “Not Null” rule. The script will also implement the “NotNull” validation rule on the applied tables.

In this example, the rule ensures every product has a name. Any row missing a product name is flagged in the ValidationResults table with its ID and marked as high severity.

 -- Example Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, Severity)
VALUES ('ProductName must not be null', 'Products', 'ProductName', 'NotNull', 'High');
 
-- Example Implementation
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, R.RuleID, R.TableName, R.ColumnName,
       CAST(P.ProductID AS NVARCHAR(200)),
       'NULL', R.Severity
FROM dbo.Products P
JOIN dbo.ValidationRules R ON R.RuleType = 'NotNull' AND R.IsActive = 1
WHERE P.ProductName IS NULL; 

For “Completeness % / threshold”, use the SQL script below to first insert the rules into the dbo.ValidationRules table, then I will run a sample SQL script for its implementation on the tables.

Note that you can apply as many rules in this rule category, but I am including a single rule for clarity.

In this example, we will define a rule that “at least 95% of customers must have phone numbers”. This example rule enforces that most of the customers have phone numbers. Where there is fewer than 95% of customers that have a phone number, the rule will be triggered and flags are issued for review.

 -- Example Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('At least 95% of customers must have phone numbers', 'Customers', 'PhoneNumber', 'Threshold', '0.95', 'Medium');
 
-- Implementation
DECLARE @Total INT, @Populated INT, @RuleID INT = 2;
SELECT @Total = COUNT(*), 
       @Populated = COUNT(PhoneNumber)
FROM dbo.Customers;
 
IF CAST(@Populated AS FLOAT) / @Total < 0.95
BEGIN
    INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
    VALUES (1, @RuleID, 'Customers', 'PhoneNumber', 'ALL', 'Below 95% completeness', 'Medium');
END 

Accuracy and Validity Rules

For “Range” rules, the script below will insert rule values into the dbo.ValidationRules table and also provide a sample approach to implement the rule in SQL. This example demonstrates a rule where the value of StandardCost in the Products table must always be between the range of 0 and 1000.

In this example, the rule ensures that no product has a cost outside of the expected range of 0–1000. Any values that are out-of-range will be flagged in the validation results so they can be corrected.

 -- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('StandardCost must be between 0-1000', 'Products', 'StandardCost', 'Range', '0-1000', 'High');
 
-- Implementation
DECLARE @Min INT = 0, @Max INT = 1000, @RuleID INT = 3;
 
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Products', 'StandardCost',
       CAST(ProductID AS NVARCHAR(200)),
       CAST(StandardCost AS NVARCHAR(200)), 'High'
FROM dbo.Products
WHERE TRY_CAST(StandardCost AS FLOAT) < @Min
   OR TRY_CAST(StandardCost AS FLOAT) > @Max; 

For “Lookup” rules, the SQL script below shows how to insert business rules for it and how to implement it in SQL. The SQL validates that Category values are only one of: Electronics, Furniture or Appliances. If there are any product categories with NULL or invalid categories, they will be flagged.

-- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('Category must be Electronics, Furniture, Appliances', 'Products', 'Category', 'Lookup', 'Electronics,Furniture,Appliances', 'Medium');
 
DECLARE @RuleID INT = 4;
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Products', 'Category',
       CAST(ProductID AS NVARCHAR(200)),
       Category, 'Medium'
FROM dbo.Products
WHERE Category IS NULL OR Category NOT IN ('Electronics','Furniture','Appliances');
 

For “Reference” rules, we are assuming that every CustomerID existing in the Orders table must also exist in the Customers table.

In this example, note that this rule ensures that orders cannot reference customers that do not exist. This rule is a simple referential integrity check, which is similar to a foreign key constraint, but it is however applied dynamically for validation purposes.

-- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('Orders.CustomerID must exist in Customers', 'Orders', 'CustomerID', 'Reference', 'Customers.CustomerID', 'High');
 
-- Implementation
DECLARE @RuleID INT = 5;
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Orders', 'CustomerID',
       CAST(O.OrderID AS NVARCHAR(200)),
       CAST(O.CustomerID AS NVARCHAR(200)), 'High'
FROM dbo.Orders O
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.Customers C WHERE C.CustomerID = O.CustomerID
); 

For “Type Check” rules, we are assuming the column AnnualSpend in the Customers table must always be a valid decimal number.

In this example, this rule ensures that AnnualSpend will only contain proper decimal values. Any record where AnnualSpend is not a decimal value i.e. where it looks like either a text, incorrectly formatted, or invalid, it will be flagged as a data quality issue.

-- Rule setup
INSERT INTO dbo.ValidationRules 
(RuleName, TableName, ColumnName, RuleType, Severity)
VALUES ('AnnualSpend must be a valid DECIMAL', 'Customers', 'AnnualSpend', 'TypeCheck', 'Medium');
 
-- Implementation
DECLARE @RuleID INT = 6;
 
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Customers', 'AnnualSpend',
       CAST(CustomerID AS NVARCHAR(200)),
       CAST(AnnualSpend AS NVARCHAR(200)), 'Medium'
FROM dbo.Customers
WHERE TRY_CAST(AnnualSpend AS DECIMAL(18,2)) IS NULL
      AND AnnualSpend IS NOT NULL;

Consistency Rules

For “Conditional” rules, the requirement is that if a product is in the Electronics category, then it must have a StandardCost greater than 0.

In this example, the rule depends on a condition: If a product is in the electronic category, it must have a valid cost. Alternatively, if it is in another category (for example Furniture or Appliances), the rule is not applicable.

-- Rule setup
INSERT INTO dbo.ValidationRules 
(RuleName, TableName, RuleType, RuleValue, Severity)
VALUES ('If Category=Electronics then StandardCost>0', 'Products', 'Conditional', 'Category=Electronics => StandardCost>0', 'High');
 
-- Implementation
DECLARE @RuleID INT = 7;
 
INSERT INTO dbo.ValidationResults 
(RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Products', 'StandardCost',
       CAST(ProductID AS NVARCHAR(200)),
       CAST(StandardCost AS NVARCHAR(200)), 'High'
FROM dbo.Products
WHERE Category = 'Electronics' 
      AND (StandardCost IS NULL OR StandardCost <= 0);
 

For “Cross-Field” rules, this rule requires that in the Orders table, the OrderDate must be before the ShipDate.

Without this rule, your system could be reporting wrong information for instance it may show that an item has been shipped even when it is not ordered yet. Thus, this rule makes sure that any time-based relationships between fields make sense.

 
-- Rule setup
INSERT INTO dbo.ValidationRules 
(RuleName, TableName, RuleType, RuleValue, Severity)
VALUES ('OrderDate must be before ShipDate', 'Orders', 'CrossField', 'OrderDate < ShipDate', 'High');
 
-- Implementation
DECLARE @RuleID INT = 8;
 
INSERT INTO dbo.ValidationResults 
(RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Orders', 'OrderDate/ShipDate',
       CAST(OrderID AS NVARCHAR(200)),
       CONCAT(OrderDate, ' > ', ShipDate), 'High'
FROM dbo.Orders
WHERE ShipDate IS NOT NULL
  AND OrderDate > ShipDate;
 

Uniqueness Rules

For “Uniqueness” rules, the script requires that each customer must have a unique email address.

Based on this example, this rule should prevent a situation where we find two different customers having the same email address.

-- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, Severity)
VALUES ('CustomerEmail must be unique', 'Customers', 'Email', 'Uniqueness', 'High');
 
-- Implementation
DECLARE @RuleID INT = 9;
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Customers', 'Email',
       Email,
       CAST(COUNT(*) AS NVARCHAR(200)), 'High'
FROM dbo.Customers
GROUP BY Email
HAVING COUNT(*) > 1;
 

Timeliness Rules

For “Timeliness” rules, we assume a rule that a customer’s LastUpdatedDate must be within the last 30 days.

Looking at this example, this rule will ensure that customer records are always fresh and flags any customer records that have not been updated in the past month.

 -- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('LastUpdatedDate must be within 30 days', 'Customers', 'LastUpdatedDate', 'Timeliness', '30', 'Medium');
 
-- Implementation
DECLARE @RuleID INT = 10;
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Customers', 'LastUpdatedDate',
       CAST(CustomerID AS NVARCHAR(200)),
       CAST(LastUpdatedDate AS NVARCHAR(200)), 'Medium'
FROM dbo.Customers
WHERE LastUpdatedDate < DATEADD(DAY, -30, GETDATE());

Pattern Rules (Regex-like Validation Rules)

For “Regex/Pattern” rules, although SQL server does not natively support true “regex” logic prior to SQL Server 2025, we can leverage “LIKE” or “PATINDEX” for not too complex patterns.

This rule will automatically find invalid email formats and then logs them for data quality review.

 -- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('Email must follow pattern *@*.*', 'Customers', 'Email', 'Regex', '%_@_%._%', 'High');
 
-- Implementation
DECLARE @RuleID INT = 11;
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Customers', 'Email',
       CAST(CustomerID AS NVARCHAR(200)),
       Email, 'High'
FROM dbo.Customers
WHERE Email NOT LIKE '%_@_%._%'; 

Statistical Rules

For “Outlier Detection” rules, I will be leveraging a simple z-score approach for this demo. The rule will automatically flag any extreme OrderAmounts based on statistics instead of a hard-coded value.

-- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, ColumnName, RuleType, RuleValue, Severity)
VALUES ('OrderAmount should not be extreme outlier', 'Orders', 'OrderAmount', 'Outlier', '3', 'Medium'); -- 3 = Z-score threshold
 
-- Implementation
DECLARE @RuleID INT = 12, @Mean FLOAT, @Std FLOAT;
SELECT @Mean = AVG(OrderAmount * 1.0), 
       @Std = STDEV(OrderAmount * 1.0)
FROM dbo.Orders;
 
INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Orders', 'OrderAmount',
       CAST(OrderID AS NVARCHAR(200)),
       CAST(OrderAmount AS NVARCHAR(200)), 'Medium'
FROM dbo.Orders
WHERE ABS((OrderAmount - @Mean) / NULLIF(@Std,0)) > 3;
 

For “Trend/Drift Check” rules, this will check if the current day’s count of orders deviates more than 20% from the average daily order count.

 -- Rule setup
INSERT INTO dbo.ValidationRules (RuleName, TableName, RuleType, RuleValue, Severity)
VALUES ('Daily Orders volume must not drift >20% from average', 'Orders', 'Drift', '0.20', 'Medium');
 
-- Implementation
DECLARE @RuleID INT = 13, @Avg FLOAT, @TodayCount INT;
SELECT @Avg = AVG(Cnt*1.0)
FROM (SELECT CAST(OrderDate AS DATE) d, COUNT(*) Cnt FROM dbo.Orders GROUP BY CAST(OrderDate AS DATE)) T;
 
SELECT @TodayCount = COUNT(*) FROM dbo.Orders WHERE CAST(OrderDate AS DATE) = CAST(GETDATE() AS DATE);
 
IF ABS(@TodayCount - @Avg) / @Avg > 0.20
BEGIN
    INSERT INTO dbo.ValidationResults (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
    VALUES (1, @RuleID, 'Orders', 'OrderDate', 'ALL', CONCAT('Today count=',@TodayCount,' Avg=',@Avg), 'Medium');
END 

Threshold Rules

An example of this rule is when we need to count how many rows in the Customers table that have a missing value e.g. PhoneNumber. Where it is found that more than 5% of all rows are missing its Phone Numbers, then this rule will be flagged as failed and store the result in ValidationResults table.

DECLARE @RuleID INT = 14,
        @Total INT = 0,
        @Nulls INT = 0,
        @Threshold FLOAT = 0.05;
 
SELECT
    @Total = COUNT(*),
    @Nulls = COUNT(*) 
FROM dbo.Customers;
 
IF @Total > 0 AND CAST(@Nulls AS FLOAT)/@Total > @Threshold
BEGIN
    INSERT INTO dbo.ValidationResults 
    (RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
    VALUES 
    (1, @RuleID, 'Customers', 'PhoneNumber', 'ALL', 
     CONCAT('Null ratio=', CAST(@Nulls AS FLOAT)/@Total), 'High');
END 

Business-Specific Rules

An example to use in this rule is where the business needs to flag those high value customers (with an annual spend of over $50,000) but don’t have an email in the database.

 -- Rule setup
INSERT INTO dbo.ValidationRules 
(RuleName, TableName, RuleType, RuleValue, Severity)
VALUES ('High-value customers must have Email', 'Customers', 'BusinessRule', 'HighValue=>HasEmail', 'High');
 
-- Implementation
DECLARE @RuleID INT = 15;
 
INSERT INTO dbo.ValidationResults 
(RunID, RuleID, TableName, ColumnName, RecordID, FailedValue, Severity)
SELECT 1, @RuleID, 'Customers', 'Email',
       CAST(CustomerID AS NVARCHAR(200)),
       'Missing Email', 'High'
FROM dbo.Customers
WHERE AnnualSpend > 50000 
      AND (Email IS NULL OR Email = ''); 

STEP 4: Loop through the ValidationRules table

Optionally, merge all the scripts into a single Stored Procedures that loops through the ValidationRules table.

In my previous post on “Simplifying Data Quality Validation with SQL”, I demonstrated an end-to-end approach to managing data quality validation using SQL. In that blog post I also showed how to merge validation rules scripts into a single Stored Procedure for ease of management. I will no be doing the merging in this blog post since you can check it out in my previous article. The purpose of this blog post was to provide a guide and demonstrate how to implement the data validation rules.

Limitations of Leveraging SQL for Data Quality Rules Implementation

While its sounds easier to implement your data quality management in SQL, there are some drawbacks that you should be aware of.

These include:

• Database Performance Issues: As your database grows, running the data validation queries might start affecting the performance of your database, so its worth paying attention to this.
• Maintenance Issues: As time goes on, maintaining these data validation queries becomes complicated and thus leading to maintenance issues. This is something you should take note of.
• Only Support SQ-Based Queries: With SQL-based data quality rules, they only support data sources that can be queried in SQL. Thus, any other sources like APIs, flat files, and cloud streaming sources will miss out in these validations.
• Increased Processing Costs: From the few examples we have described in this article, we can see that with many data tables there is a corresponding need for more data quality queries and rules. Thus, this will eventually lead to the need to increase infrastructure capacity, which in turn leads to costs for any organization.
• With SQL-based data quality rules implementation, you are basically restricted to what data quality issues you are aware of. If by any chance you missed some rules while setting up the rules and implementation, or you did not foresee certain rules or data quality issues that turned up later on, you might end up not solving all the issues.

In summary, this guide has shown that it is relatively easy to implement and automate validation, capture detailed results, and continuously monitor data quality with SQL. It would be great to hear from the community about how they have implemented these rules using SQL in their organization.

Next Steps

• Read more about implementation of data quality checks in my previous post Simplifying data quality validation with SQL
• Read about how to do data quality checks in SQL in this TELMAI article.
• Check out this Microsoft documentation on data quality rules here.
• Read my other posts here.