different advanced topics — each with a real-world use case, code example, and detailed explanation. These examples build on what you already know (SELECT, JOIN, INSERT, UPDATE), but show how to go further with SQL Server's features.

 

🔸 1. Common Table Expressions (CTEs)

✅ Use Case: Get the second most recent order per customer.

WITH OrderRanks AS (
    SELECT 
        CustomerID,
        OrderID,
        OrderDate,
        RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS OrderRank
    FROM Orders
)
SELECT * 
FROM OrderRanks
WHERE OrderRank = 2;

🔍 Explanation:

• WITH OrderRanks AS (...) defines a CTE.

• RANK() assigns ranks per customer based on OrderDate.

• We filter only for OrderRank = 2 → gives the second latest order per customer.

---

🔸 2. CASE Statements (Conditional Logic)

✅ Use Case: Categorize customers by country.

SELECT 
    CustomerID,
    Name,
    Country,
    CASE 
        WHEN Country IN ('USA', 'Canada') THEN 'North America'
        WHEN Country IN ('France', 'Germany') THEN 'Europe'
        ELSE 'Other'
    END AS Region
FROM Customers;

🔍 Explanation:

• CASE adds if-else logic inside a query.

• This example labels customers by region.

---

🔸 3. Window Functions – LAG/LEAD

✅ Use Case: Compare each customer’s latest order to their previous one.

SELECT 
    CustomerID,
    OrderDate,
    TotalAmount,
    LAG(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousAmount
FROM Orders;

🔍 Explanation:

• LAG() looks backward at the previous row.

• Shows how a customer’s spending changed from their last order.

---

🔸 4. Stored Procedure with Parameters

✅ Use Case: Create a procedure that shows all orders above a certain amount.

CREATE PROCEDURE GetLargeOrders
    @MinAmount DECIMAL(10, 2)
AS
BEGIN
    SELECT * FROM Orders WHERE TotalAmount > @MinAmount
END;

To run:

EXEC GetLargeOrders @MinAmount = 500;

🔍 Explanation:

• Stored procedures let you encapsulate queries for reuse.

• You can pass input via parameters like @MinAmount.

---

🔸 5. Triggers

✅ Use Case: Automatically log when a customer is deleted.

CREATE TABLE CustomerLogs (
    CustomerID INT,
    DeletedAt DATETIME
);

CREATE TRIGGER trg_CustomerDelete
ON Customers
AFTER DELETE
AS
BEGIN
    INSERT INTO CustomerLogs (CustomerID, DeletedAt)
    SELECT CustomerID, GETDATE() FROM DELETED;
END;

🔍 Explanation:

• This AFTER DELETE trigger fires automatically.

• It inserts a record into CustomerLogs every time a customer is deleted.

---

🔸 6. Transactions (Atomic Operations)

✅ Use Case: Insert order and details together, or not at all.

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
    VALUES (1, GETDATE(), 300);

    DECLARE @OrderID INT = SCOPE_IDENTITY();

    INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
    VALUES (@OrderID, 1001, 2, 150);

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Something went wrong: ' + ERROR_MESSAGE();
END CATCH;

🔍 Explanation:

• Ensures that both Orders and OrderDetails insert succeed together.

• SCOPE_IDENTITY() grabs the last inserted ID.

• If anything fails, ROLLBACK cancels it all.

---

🔸 7. Views

✅ Use Case: Create a simplified, reusable customer order summary.

CREATE VIEW vw_CustomerSummary AS
SELECT 
    c.Name,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;

-- Use the view
SELECT * FROM vw_CustomerSummary WHERE TotalAmount > 1000;

🔍 Explanation:

• Views let you hide complexity.

• Acts like a virtual table based on SELECT logic.

---

🔸 8. Indexing

✅ Use Case: Speed up queries filtering on OrderDate.

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON Orders(OrderDate);

🔍 Explanation:

• Indexes improve performance on WHERE, JOIN, and ORDER BY clauses.

• But too many indexes can slow down inserts/updates.

---

🔸 9. Temporary Tables

✅ Use Case: Store intermediate results for complex queries.

SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
INTO #CustomerTotals
FROM Orders
GROUP BY CustomerID;

SELECT * FROM #CustomerTotals WHERE TotalSpent > 1000;

🔍 Explanation:

• #CustomerTotals is a temporary table that exists during the session.

• Useful for breaking down complex steps.

---

🔸 10. Dynamic SQL

✅ Use Case: Build a query based on input values.

DECLARE @TableName NVARCHAR(100) = 'Customers';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC sp_executesql @SQL;

🔍 Explanation:

• Lets you build and execute queries at runtime.

• Useful when table names or columns are dynamic.

---

✅ Summary Table

Topic	Key Feature	Use Case
CTE	Temporary result set	Second latest order per customer
CASE	Conditional logic	Label customers by region
Window Functions	LAG / LEAD	Compare current vs previous order
Stored Procedure	Parameterized logic	Reusable large order query
Trigger	Automatic action	Log customer deletions
Transaction	Atomic multi-step ops	Insert order & details together
View	Virtual table	Simplify query logic
Index	Performance optimization	Fast filtering on OrderDate
Temp Table	Session-level storage	Intermediate data processing
Dynamic SQL	Runtime SQL generation	Query variable table names