TSQL- Uniformity,Independence,Containment in Cardinality Estimation in SQL Server

 To explore the concepts of uniformity, independence, and containment in cardinality estimation in SQL Server, you can create and execute T-SQL queries that illustrate these principles. In this example, I'll use a simplified dataset to demonstrate these concepts.

First, let's create a sample table and populate it with data:

sql
-- Create a sample table
CREATE TABLE SampleData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CategoryID INT,
    ProductID INT,
    Quantity INT
);

-- Insert sample data
INSERT INTO SampleData (CategoryID, ProductID, Quantity)
VALUES
    (1, 101, 5),
    (1, 102, 10),
    (1, 103, 3),
    (2, 201, 7),
    (2, 202, 2),
    (3, 301, 15),
    (3, 302, 8);

Now, let's write queries that illustrate each of the three concepts:

1. Uniformity:

   In this example, we want to check the uniformity of the "Quantity" column, i.e., whether the distribution of values is roughly balanced.

   sql
   -- Uniformity Query
   SELECT
       Quantity,
       COUNT(*) AS Count
   FROM SampleData
   GROUP BY Quantity
   ORDER BY Quantity;
   

   The result of this query will show you the distribution of the "Quantity" values.

2. Independence:

   We'll check for independence by examining whether there is a significant relationship between the "Quantity" and "CategoryID" columns. Ideally, these columns should be independent of each other.

   sql
   -- Independence Query
   SELECT
       CategoryID,
       AVG(Quantity) AS AvgQuantity
   FROM SampleData
   GROUP BY CategoryID;
   

   If there is no significant correlation between "CategoryID" and the average "Quantity," it suggests independence.

3. Containment:

   We'll create a query that demonstrates containment by applying conditions on both "CategoryID" and "Quantity."

   sql
   -- Containment Query
   SELECT
       CategoryID,
       SUM(Quantity) AS TotalQuantity
   FROM SampleData
   WHERE CategoryID = 1 AND Quantity > 5
   GROUP BY CategoryID;
   

   This query shows how SQL Server can estimate the cardinality of a result set when multiple conditions are applied to different columns.