Master SQL Server Module 3: Comprehensive Guide to Basic T-SQL Programming

 

Table of Contents

1. Overview of T-SQL Programming

   • What is T-SQL?

   • Why Learn T-SQL for SQL Server?

   • Scope of Module 3

2. SELECT, FROM, WHERE, and ORDER BY

   • Understanding the SELECT Statement

   • Using the FROM Clause

   • Filtering with WHERE

   • Sorting with ORDER BY

   • Real-Life Examples

   • Pros and Cons

   • Best Practices

   • Security and Performance Considerations

   • Error Handling

3. INSERT, UPDATE, and DELETE Statements

   • Inserting Data with INSERT

   • Modifying Data with UPDATE

   • Removing Data with DELETE

   • Real-Life Examples

   • Pros and Cons

   • Best Practices

   • Security and Performance Considerations

   • Error Handling

4. Aggregate Functions (SUM, AVG, MIN, MAX, COUNT)

   • Overview of Aggregate Functions

   • Using SUM, AVG, MIN, MAX, and COUNT

   • Grouping Data with GROUP BY

   • Filtering Groups with HAVING

   • Real-Life Examples

   • Pros and Cons

   • Best Practices

   • Security and Performance Considerations

   • Error Handling

5. Joins (INNER, LEFT, RIGHT, FULL, CROSS)

   • Understanding SQL Joins

   • Types of Joins: INNER, LEFT, RIGHT, FULL, CROSS

   • Real-Life Examples

   • Pros and Cons

   • Best Practices

   • Security and Performance Considerations

   • Error Handling

6. Subqueries and Common Table Expressions (CTEs)

   • What Are Subqueries?

   • Types of Subqueries: Single-Row, Multi-Row, Correlated

   • Introduction to CTEs

   • Recursive CTEs

   • Real-Life Examples

   • Pros and Cons

   • Best Practices

   • Security and Performance Considerations

   • Error Handling

7. SQL Server 2025: Approximate Count Distinct Improvements

   • What is Approximate Count Distinct?

   • How It Works in SQL Server 2025

   • Use Cases and Examples

   • Pros and Cons

   • Best Practices

   • Security and Performance Considerations

   • Error Handling

8. Alternatives to T-SQL Programming

   • Other Query Languages and Tools

   • Comparing T-SQL with MySQL, PostgreSQL, and Oracle SQL

   • When to Use Alternatives

9. Conclusion

   • Key Takeaways

   • Next Steps in Mastering SQL Server

---

1. Overview of T-SQL Programming

What is T-SQL?

Transact-SQL (T-SQL) is Microsoft’s proprietary extension of SQL, designed specifically for SQL Server. It builds on standard SQL by adding procedural programming capabilities, such as variables, loops, and error handling, making it ideal for complex database operations. T-SQL allows you to query, manipulate, and manage data in SQL Server databases efficiently.

Why Learn T-SQL for SQL Server?

• Industry Standard: T-SQL is widely used in enterprises relying on SQL Server for data management.

• Versatility: Supports both simple queries and advanced programming constructs.

• Performance: Optimized for SQL Server, offering superior performance for large-scale applications.

• Integration: Seamlessly integrates with Microsoft tools like SSMS, Azure, and Power BI.

Scope of Module 3

This module focuses on foundational T-SQL programming, covering essential commands and techniques to query and manipulate data. We’ll explore practical examples, best practices, and advanced features like SQL Server 2025’s Approximate Count Distinct, ensuring you gain hands-on skills for real-world applications.

---

2. SELECT, FROM, WHERE, and ORDER BY

The SELECT, FROM, WHERE, and ORDER BY clauses form the core of T-SQL querying, enabling you to retrieve and sort data from SQL Server databases.

Understanding the SELECT Statement

The SELECT statement retrieves data from one or more tables. It specifies the columns to return and can include expressions, functions, or aliases.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC | DESC];

Using the FROM Clause

The FROM clause specifies the table(s) from which to retrieve data. It can reference a single table, multiple tables (via joins), or views.

Filtering with WHERE

The WHERE clause filters rows based on a condition, allowing you to retrieve only the data that meets specific criteria.

Sorting with ORDER BY

The ORDER BY clause sorts the result set by one or more columns, either in ascending (ASC) or descending (DESC) order.

Real-Life Example: Retail Inventory Management

Imagine you manage a retail database with a Products table containing product details. You want to retrieve products with a price greater than $50, sorted by price in descending order.

Sample Table: Products

ProductID	ProductName	Price	Category
1	Laptop	999.99	Electronics
2	Mouse	29.99	Accessories
3	Smartphone	699.99	Electronics
4	Keyboard	59.99	Accessories

Query:

SELECT ProductID, ProductName, Price
FROM Products
WHERE Price > 50
ORDER BY Price DESC;

Result:

ProductID	ProductName	Price
1	Laptop	999.99
3	Smartphone	699.99
4	Keyboard	59.99

Explanation:

• SELECT retrieves ProductID, ProductName, and Price.

• FROM Products specifies the source table.

• WHERE Price > 50 filters products priced above $50.

• ORDER BY Price DESC sorts the results by price in descending order.

Pros and Cons

Pros:

• Flexibility: SELECT supports complex expressions and functions.

• Readability: WHERE and ORDER BY make queries intuitive.

• Efficiency: Filtering with WHERE reduces the dataset early.

Cons:

• Performance Overhead: Sorting large datasets with ORDER BY can be slow without proper indexing.

• Complexity: Complex WHERE conditions may impact readability.

Best Practices

1. Use Specific Columns: Avoid SELECT * to reduce unnecessary data retrieval.

2. Index Filtered Columns: Create indexes on columns used in WHERE and ORDER BY for faster queries.

3. Use Aliases: Assign meaningful aliases to columns for clarity.

4. Optimize Conditions: Place restrictive conditions in WHERE to filter data early.

Security Considerations

• Prevent SQL Injection: Use parameterized queries to sanitize user inputs.

  -- Parameterized query
  DECLARE @MinPrice DECIMAL(10,2) = 50;
  SELECT ProductID, ProductName, Price
  FROM Products
  WHERE Price > @MinPrice
  ORDER BY Price DESC;

• Restrict Access: Grant SELECT permissions only to authorized users.

Performance Considerations

• Indexing: Create indexes on columns used in WHERE and ORDER BY.

• Avoid Functions in WHERE: Functions on columns (e.g., WHERE UPPER(ProductName) = 'LAPTOP') prevent index usage.

• Use Query Execution Plans: Analyze plans in SQL Server Management Studio (SSMS) to identify bottlenecks.

Error Handling

Wrap queries in TRY-CATCH blocks to handle errors gracefully.

BEGIN TRY
    SELECT ProductID, ProductName, Price
    FROM Products
    WHERE Price > 50
    ORDER BY Price DESC;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Real-Life Example: Sales Reporting

A sales manager needs to generate a report of high-value orders (above $100) from the Orders table, sorted by order date.

Sample Table: Orders

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2025-01-10	150.00
102	2	2025-02-15	75.00
103	1	2025-03-01	200.00

Query:

BEGIN TRY
    SELECT OrderID, CustomerID, OrderDate, TotalAmount
    FROM Orders
    WHERE TotalAmount > 100
    ORDER BY OrderDate ASC;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2025-01-10	150.00
103	1	2025-03-01	200.00

Explanation:

• Filters orders with TotalAmount > 100.

• Sorts by OrderDate in ascending order.

• Uses TRY-CATCH to handle potential errors, such as missing tables.

---

3. INSERT, UPDATE, and DELETE Statements

The INSERT, UPDATE, and DELETE statements are used to manipulate data in SQL Server tables, enabling you to add, modify, or remove records.

Inserting Data with INSERT

The INSERT statement adds new rows to a table.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Modifying Data with UPDATE

The UPDATE statement modifies existing rows based on a condition.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Removing Data with DELETE

The DELETE statement removes rows that match a condition.

Syntax:

DELETE FROM table_name
WHERE condition;

Real-Life Example: Customer Management

Consider a Customers table for managing customer data in a CRM system.

Sample Table: Customers

CustomerID	FirstName	LastName	Email
1	John	Doe	john.doe@email.com
2	Jane	Smith	jane.smith@email.com

INSERT Example:

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Alice', 'Johnson', 'alice.johnson@email.com');

Result:

CustomerID	FirstName	LastName	Email
1	John	Doe	john.doe@email.com
2	Jane	Smith	jane.smith@email.com
3	Alice	Johnson	alice.johnson@email.com

UPDATE Example:

UPDATE Customers
SET Email = 'john.doe@newemail.com'
WHERE CustomerID = 1;

Result:

CustomerID	FirstName	LastName	Email
1	John	Doe	john.doe@newemail.com
2	Jane	Smith	jane.smith@email.com
3	Alice	Johnson	alice.johnson@email.com

DELETE Example:

DELETE FROM Customers
WHERE CustomerID = 2;

Result:

CustomerID	FirstName	LastName	Email
1	John	Doe	john.doe@newemail.com
3	Alice	Johnson	alice.johnson@email.com

Pros and Cons

Pros:

• Ease of Use: Simple syntax for data manipulation.

• Flexibility: Supports bulk operations and conditional updates.

• Atomicity: Can be used within transactions for data integrity.

Cons:

• Risk of Data Loss: DELETE without WHERE can remove all rows.

• Performance Impact: Large UPDATE or DELETE operations can be slow without indexing.

Best Practices

1. Use Transactions: Wrap INSERT, UPDATE, and DELETE in transactions to ensure data consistency.

   BEGIN TRANSACTION;
   INSERT INTO Customers (FirstName, LastName, Email)
   VALUES ('Bob', 'Wilson', 'bob.wilson@email.com');
   COMMIT;

2. Validate Data: Use constraints (e.g., NOT NULL, CHECK) to ensure data integrity.

3. Limit Scope: Always include a WHERE clause in UPDATE and DELETE to avoid unintended changes.

4. Backup Data: Back up tables before performing large DELETE operations.

Security Considerations

• Prevent SQL Injection: Use parameterized queries or stored procedures.

  DECLARE @FirstName NVARCHAR(50) = 'Bob';
  DECLARE @LastName NVARCHAR(50) = 'Wilson';
  DECLARE @Email NVARCHAR(100) = 'bob.wilson@email.com';
  INSERT INTO Customers (FirstName, LastName, Email)
  VALUES (@FirstName, @LastName, @Email);

• Restrict Permissions: Grant INSERT, UPDATE, and DELETE permissions only to authorized roles.

Performance Considerations

• Indexing: Ensure indexes exist on columns used in WHERE clauses.

• Batch Operations: For large datasets, process data in batches to avoid locking issues.

  WHILE EXISTS (SELECT 1 FROM Customers WHERE LastName = 'Smith')
  BEGIN
      DELETE TOP (1000) FROM Customers WHERE LastName = 'Smith';
  END;

• Avoid Triggers: Minimize triggers on frequently updated tables to reduce overhead.

Error Handling

Use TRY-CATCH to handle errors during data manipulation.

BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO Customers (FirstName, LastName, Email)
    VALUES ('Bob', 'Wilson', 'bob.wilson@email.com');
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Real-Life Example: Inventory Updates

A warehouse manager needs to update stock quantities and remove discontinued products.

Sample Table: Inventory

ProductID	ProductName	Quantity
1	Laptop	50
2	Mouse	100
3	Keyboard	0

Query:

BEGIN TRY
    BEGIN TRANSACTION;
    -- Insert new product
    INSERT INTO Inventory (ProductID, ProductName, Quantity)
    VALUES (4, 'Monitor', 25);
    -- Update stock
    UPDATE Inventory
    SET Quantity = Quantity + 10
    WHERE ProductID = 1;
    -- Delete discontinued product
    DELETE FROM Inventory
    WHERE Quantity = 0;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

ProductID	ProductName	Quantity
1	Laptop	60
2	Mouse	100
4	Monitor	25

Explanation:

• Inserts a new product (Monitor).

• Increases Laptop stock by 10.

• Deletes products with zero quantity.

• Uses a transaction to ensure all operations succeed or fail together.

---

4. Aggregate Functions (SUM, AVG, MIN, MAX, COUNT)

Aggregate functions perform calculations on a set of rows and return a single value, often used with GROUP BY to summarize data.

Overview of Aggregate Functions

• SUM: Calculates the total of a numeric column.

• AVG: Computes the average of a numeric column.

• MIN: Finds the smallest value in a column.

• MAX: Finds the largest value in a column.

• COUNT: Counts the number of rows or non-null values.

Using SUM, AVG, MIN, MAX, and COUNT

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Grouping Data with GROUP BY

The GROUP BY clause groups rows with the same values in specified columns, enabling aggregate calculations.

Filtering Groups with HAVING

The HAVING clause filters grouped results, used with aggregate functions since WHERE cannot filter aggregates.

Real-Life Example: Sales Analysis

A retail company wants to analyze sales by category.

Sample Table: Sales

SaleID	ProductID	CategoryID	SaleAmount
1	1	1	999.99
2	2	2	29.99
3	3	1	699.99
4	4	2	59.99

Query:

SELECT CategoryID,
       COUNT(*) AS SaleCount,
       SUM(SaleAmount) AS TotalSales,
       AVG(SaleAmount) AS AvgSale,
       MIN(SaleAmount) AS MinSale,
       MAX(SaleAmount) AS MaxSale
FROM Sales
GROUP BY CategoryID
HAVING COUNT(*) > 1;

Result:

CategoryID	SaleCount	TotalSales	AvgSale	MinSale	MaxSale
1	2	1699.98	849.99	699.99	999.99
2	2	89.98	44.99	29.99	59.99

Explanation:

• Groups sales by CategoryID.

• Calculates count, sum, average, minimum, and maximum sales per category.

• Filters categories with more than one sale using HAVING.

Pros and Cons

Pros:

• Summarization: Simplifies large datasets into meaningful metrics.

• Flexibility: Works with GROUP BY for detailed analysis.

• Compatibility: Supported across all SQL Server versions.

Cons:

• Performance: Aggregating large datasets can be slow without indexing.

• Null Handling: Null values are ignored, which may skew results.

Best Practices

1. Use Indexes: Index columns used in GROUP BY and HAVING.

2. Filter Early: Apply WHERE before GROUP BY to reduce rows.

3. Handle Nulls: Use COALESCE or ISNULL to manage null values.

   SELECT CategoryID, COUNT(COALESCE(SaleAmount, 0)) AS SaleCount
   FROM Sales
   GROUP BY CategoryID;

4. Avoid Over-Aggregation: Limit aggregate functions to necessary calculations.

Security Considerations

• Restrict Data Access: Use views to limit exposure of sensitive data.

• Sanitize Inputs: Avoid dynamic SQL with user inputs in aggregate queries.

Performance Considerations

• Indexing: Create covering indexes for GROUP BY columns.

• Partitioning: Use table partitioning for large datasets.

• Query Optimization: Use EXPLAIN ANALYZE to identify slow aggregations.

Error Handling

Handle errors in aggregate queries with TRY-CATCH.

BEGIN TRY
    SELECT CategoryID, COUNT(*) AS SaleCount
    FROM Sales
    GROUP BY CategoryID
    HAVING COUNT(*) > 1;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Real-Life Example: Employee Performance

A company wants to evaluate employee sales performance by department.

Sample Table: Employees

EmployeeID	Department	Sales
1	Sales	5000
2	Sales	7000
3	HR	NULL
4	Sales	6000

Query:

BEGIN TRY
    SELECT Department,
           COUNT(*) AS EmployeeCount,
           SUM(ISNULL(Sales, 0)) AS TotalSales,
           AVG(ISNULL(Sales, 0)) AS AvgSales
    FROM Employees
    GROUP BY Department
    HAVING COUNT(*) > 1;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

Department	EmployeeCount	TotalSales	AvgSales
Sales	3	18000	6000

Explanation:

• Groups employees by Department.

• Uses ISNULL to handle null sales values.

• Filters departments with more than one employee.

---

5. Joins (INNER, LEFT, RIGHT, FULL, CROSS)

Joins combine data from multiple tables based on related columns, enabling complex data retrieval.

Understanding SQL Joins

• INNER JOIN: Returns only matching rows from both tables.

• LEFT JOIN: Returns all rows from the left table, with matching rows from the right table (nulls for non-matches).

• RIGHT JOIN: Returns all rows from the right table, with matching rows from the left table.

• FULL JOIN: Returns all rows from both tables, with nulls for non-matches.

• CROSS JOIN: Returns the Cartesian product of both tables.

Syntax:

SELECT columns
FROM table1
[INNER | LEFT | RIGHT | FULL | CROSS] JOIN table2
ON table1.column = table2.column;

Real-Life Example: Order Processing

A retail system tracks orders and customers.

Sample Tables: Customers

CustomerID	FirstName	LastName
1	John	Doe
2	Jane	Smith

Orders

OrderID	CustomerID	TotalAmount
101	1	150.00
102	1	200.00
103	3	75.00

INNER JOIN Example:

SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.TotalAmount
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;

Result:

CustomerID	FirstName	LastName	OrderID	TotalAmount
1	John	Doe	101	150.00
1	John	Doe	102	200.00

LEFT JOIN Example:

SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.TotalAmount
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;

Result:

CustomerID	FirstName	LastName	OrderID	TotalAmount
1	John	Doe	101	150.00
1	John	Doe	102	200.00
2	Jane	Smith	NULL	NULL

RIGHT JOIN Example:

SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.TotalAmount
FROM Customers c
RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID;

Result:

CustomerID	FirstName	LastName	OrderID	TotalAmount
1	John	Doe	101	150.00
1	John	Doe	102	200.00
NULL	NULL	NULL	103	75.00

FULL JOIN Example:

SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.TotalAmount
FROM Customers c
FULL JOIN Orders o
ON c.CustomerID = o.CustomerID;

Result:

CustomerID	FirstName	LastName	OrderID	TotalAmount
1	John	Doe	101	150.00
1	John	Doe	102	200.00
2	Jane	Smith	NULL	NULL
NULL	NULL	NULL	103	75.00

CROSS JOIN Example:

SELECT c.FirstName, c.LastName, o.OrderID
FROM Customers c
CROSS JOIN Orders o;

Result:

FirstName	LastName	OrderID
John	Doe	101
John	Doe	102
John	Doe	103
Jane	Smith	101
Jane	Smith	102
Jane	Smith	103

Pros and Cons

Pros:

• Data Integration: Combines data from multiple tables.

• Flexibility: Supports various join types for different use cases.

• Efficiency: Optimized with proper indexing.

Cons:

• Performance: Joins on large tables can be slow without indexes.

• Complexity: Multiple joins can make queries hard to read.

Best Practices

1. Use Explicit Joins: Prefer INNER JOIN over implicit joins (e.g., FROM table1, table2 WHERE ...).

2. Index Join Columns: Create indexes on columns used in ON clauses.

3. Minimize Joins: Limit the number of joins to improve performance.

4. Use Aliases: Assign short, meaningful aliases to tables.

Security Considerations

• Limit Data Exposure: Use views to restrict access to sensitive columns.

• Sanitize Inputs: Avoid dynamic SQL in join conditions.

Performance Considerations

• Indexing: Create indexes on join columns.

• Filter Early: Apply WHERE conditions before joining to reduce rows.

• Avoid CROSS JOIN: Use sparingly, as it generates large result sets.

Error Handling

BEGIN TRY
    SELECT c.CustomerID, c.FirstName, o.OrderID
    FROM Customers c
    INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Real-Life Example: E-Commerce Reporting

An e-commerce platform needs to report customer orders with details.

Query:

BEGIN TRY
    SELECT c.FirstName, c.LastName, SUM(o.TotalAmount) AS TotalSpent
    FROM Customers c
    LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID
    GROUP BY c.FirstName, c.LastName;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

FirstName	LastName	TotalSpent
John	Doe	350.00
Jane	Smith	0.00

Explanation:

• Uses LEFT JOIN to include customers without orders.

• Aggregates total spending per customer.

---

6. Subqueries and Common Table Expressions (CTEs)

Subqueries and CTEs enable complex data retrieval by nesting queries or defining temporary result sets.

What Are Subqueries?

A subquery is a query nested within another query, often used in the WHERE, SELECT, or FROM clause.

Types of Subqueries:

• Single-Row: Returns one row, used with operators like =, >.

• Multi-Row: Returns multiple rows, used with IN, ANY, ALL.

• Correlated: References the outer query, executed for each row.

Introduction to CTEs

A CTE is a temporary, named result set defined within a query, improving readability and reusability.

Syntax:

WITH cte_name AS (
    SELECT columns
    FROM table_name
    WHERE condition
)
SELECT columns
FROM cte_name;

Recursive CTEs

Recursive CTEs reference themselves to process hierarchical or tree-structured data.

Real-Life Example: Employee Hierarchy

A company tracks employee reporting structures.

Sample Table: Employees

EmployeeID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2

Subquery Example:

SELECT Name
FROM Employees
WHERE ManagerID = (SELECT EmployeeID FROM Employees WHERE Name = 'Alice');

Result:

Name
Bob
Carol

CTE Example:

WITH ManagerCTE AS (
    SELECT EmployeeID, Name
    FROM Employees
    WHERE ManagerID IS NULL
)
SELECT e.Name
FROM Employees e
INNER JOIN ManagerCTE m
ON e.ManagerID = m.EmployeeID;

Result:

Name
Bob
Carol

Recursive CTE Example:

WITH RecursiveCTE AS (
    SELECT EmployeeID, Name, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID, r.Level + 1
    FROM Employees e
    INNER JOIN RecursiveCTE r
    ON e.ManagerID = r.EmployeeID
)
SELECT Name, Level
FROM RecursiveCTE;

Result:

Name	Level
Alice	1
Bob	2
Carol	2
Dave	3

Pros and Cons

Pros:

• Modularity: CTEs improve query readability and maintenance.

• Flexibility: Subqueries support complex filtering.

• Hierarchical Data: Recursive CTEs handle tree structures.

Cons:

• Performance: Correlated subqueries can be slow.

• Complexity: Nested subqueries reduce readability.

Best Practices

1. Prefer CTEs: Use CTEs over subqueries for better readability.

2. Optimize Subqueries: Avoid correlated subqueries when possible; use joins instead.

3. Limit Recursion: Ensure recursive CTEs have a termination condition.

4. Index Columns: Index columns used in subquery conditions.

Security Considerations

• Sanitize Inputs: Avoid dynamic SQL in subqueries.

• Limit Scope: Use CTEs to encapsulate sensitive logic.

Performance Considerations

• Use Joins: Replace correlated subqueries with joins for better performance.

• Temporary Tables: For large datasets, consider temporary tables over CTEs.

• Query Plans: Analyze execution plans to optimize subqueries.

Error Handling

BEGIN TRY
    WITH SalesCTE AS (
        SELECT ProductID, SUM(SaleAmount) AS TotalSales
        FROM Sales
        GROUP BY ProductID
    )
    SELECT p.ProductName, s.TotalSales
    FROM Products p
    INNER JOIN SalesCTE s
    ON p.ProductID = s.ProductID;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Real-Life Example: Product Sales Analysis

A retail company wants to find products with above-average sales.

Query:

BEGIN TRY
    WITH AvgSalesCTE AS (
        SELECT AVG(SaleAmount) AS AvgSale
        FROM Sales
    )
    SELECT p.ProductName, s.SaleAmount
    FROM Sales s
    INNER JOIN Products p
    ON s.ProductID = p.ProductID
    CROSS JOIN AvgSalesCTE a
    WHERE s.SaleAmount > a.AvgSale;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

ProductName	SaleAmount
Laptop	999.99
Smartphone	699.99

Explanation:

• CTE calculates the average sale amount.

• Main query retrieves products with sales above the average.

---

7. SQL Server 2025: Approximate Count Distinct Improvements

What is Approximate Count Distinct?

SQL Server 2025 introduces enhancements to the APPROX_COUNT_DISTINCT function, which provides an approximate count of unique values in a column, optimized for large datasets.

How It Works in SQL Server 2025

APPROX_COUNT_DISTINCT uses probabilistic algorithms (e.g., HyperLogLog) to estimate distinct counts with high accuracy and low resource usage, ideal for big data scenarios.

Syntax:

SELECT APPROX_COUNT_DISTINCT(column_name)
FROM table_name;

Use Cases and Examples

Example: Customer AnalysisA company wants to estimate unique customers in a large sales dataset.

Sample Table: Sales

SaleID	CustomerID	SaleAmount
1	1	999.99
2	2	29.99
3	1	699.99
4	3	59.99

Query:

SELECT APPROX_COUNT_DISTINCT(CustomerID) AS UniqueCustomers
FROM Sales;

Result:

UniqueCustomers
3

Explanation:

• Estimates the number of unique CustomerID values.

• Faster than COUNT(DISTINCT CustomerID) for large datasets.

Pros and Cons

Pros:

• Performance: Significantly faster than exact COUNT DISTINCT.

• Scalability: Ideal for big data environments.

• Low Memory: Uses minimal resources.

Cons:

• Approximation: May introduce slight inaccuracies (typically <1% error).

• Limited Use Cases: Not suitable for scenarios requiring exact counts.

Best Practices

1. Use for Large Datasets: Apply APPROX_COUNT_DISTINCT when exact counts are unnecessary.

2. Validate Accuracy: Test results against COUNT DISTINCT for small datasets.

3. Combine with Filters: Use WHERE to reduce the dataset before counting.

Security Considerations

• Restrict Access: Limit access to sensitive columns used in APPROX_COUNT_DISTINCT.

• Avoid Dynamic SQL: Use parameterized queries for safety.

Performance Considerations

• Optimize Filters: Apply WHERE clauses to reduce data processed.

• Partition Tables: Use partitioning for large tables to enhance performance.

Error Handling

BEGIN TRY
    SELECT APPROX_COUNT_DISTINCT(CustomerID) AS UniqueCustomers
    FROM Sales;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Real-Life Example: Website Analytics

A website tracks user visits and wants to estimate unique visitors.

Sample Table: Visits

VisitID	UserID	VisitDate
1	101	2025-01-01
2	102	2025-01-01
3	101	2025-01-02
4	103	2025-01-02

Query:

BEGIN TRY
    SELECT APPROX_COUNT_DISTINCT(UserID) AS UniqueVisitors
    FROM Visits
    WHERE VisitDate >= '2025-01-01';
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

UniqueVisitors
3

Explanation:

• Estimates unique visitors in January 2025.

• Uses APPROX_COUNT_DISTINCT for fast processing.

---

8. Alternatives to T-SQL Programming

While T-SQL is powerful for SQL Server, other query languages and tools may be suitable for specific use cases.

Other Query Languages and Tools

• MySQL: Open-source, simpler syntax, ideal for web applications.

• PostgreSQL: Advanced features like JSON support, suitable for analytics.

• Oracle SQL: Enterprise-grade, robust for large-scale systems.

• NoSQL: MongoDB or Cassandra for unstructured data.

• ETL Tools: SSIS, Talend, or Informatica for data integration.

Comparing T-SQL with MySQL, PostgreSQL, and Oracle SQL

Feature	T-SQL (SQL Server)	MySQL	PostgreSQL	Oracle SQL
Procedural Programming	Yes (T-SQL)	Limited	Yes (PL/pgSQL)	Yes (PL/SQL)
Performance	High (with indexing)	High	High	High
Open-Source	No	Yes	Yes	No
JSON Support	Yes	Yes	Advanced	Yes

When to Use Alternatives

• MySQL: For cost-effective, web-based applications.

• PostgreSQL: For analytics or geospatial data.

• Oracle SQL: For enterprise systems with high reliability needs.

• NoSQL: For unstructured or semi-structured data.

---

9. Conclusion

Module 3 of Master SQL Server has equipped you with the foundational skills of T-SQL programming, covering SELECT, FROM, WHERE, ORDER BY, INSERT, UPDATE, DELETE, aggregate functions, joins, subqueries, CTEs, and SQL Server 2025’s Approximate Count Distinct. Through detailed explanations, realistic examples, and best practices, you’ve learned how to query and manipulate data effectively while prioritizing security, performance, and error handling.

Key Takeaways

• Core Queries: Master SELECT, WHERE, and ORDER BY for data retrieval.

• Data Manipulation: Use INSERT, UPDATE, and DELETE with transactions for consistency.

• Aggregations: Leverage SUM, AVG, MIN, MAX, and COUNT for data analysis.

• Joins: Combine tables efficiently with INNER, LEFT, RIGHT, FULL, and CROSS joins.

• Subqueries and CTEs: Simplify complex queries with modular structures.

• Approximate Count Distinct: Utilize SQL Server 2025’s feature for big data scenarios.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone 🎯 Master SQL Server: Complete Guide to Database Design, T-SQL & Advanced Features