SQL Snippets & Database Tips

📖 About This Guide

Database work can seem intimidating, but with the right snippets in your toolkit, you'll be querying data like a seasoned professional! This collection includes practical SQL examples for common tasks - from date formatting to finding duplicates.

Each snippet is like a tried-and-true recipe - tested, reliable, and ready to use in your own projects. Whether you're a beginner learning the basics or an experienced developer looking for a quick reference, these examples will serve you well.

📅 Date & Time Operations

ISO 8601 Date Conversion

Convert any date to the standard ISO format with UTC timezone marker. Perfect for APIs and data exchange!

SELECT 
    RTRIM(
        CAST(
            CONVERT(
                VARCHAR(50),
                DATEADD(
                    SECOND,
                    DATEDIFF(SECOND, GETDATE(), GETUTCDATE()),
                    '08/26/2014 08:18:00'
                ),
                127
            ) AS VARCHAR
        )
    ) + 'Z' AS ISO_Date;

💡 Pro Tip: This converts your local time to UTC and adds that 'Z' at the end - just like how international recipes need standardized measurements!

Date Range Queries

Find records within a specific date range - the safe way that handles time zones properly.

SELECT *
FROM Orders
WHERE OrderDate >= '2015-01-01'
  AND OrderDate <  '2015-02-01';

🎯 Why This Works: Using >= and < instead of BETWEEN avoids timezone confusion at midnight!

🔄 Data Transformation Magic

PIVOT - Turn Rows into Columns

Transform your data from long format to wide format. Great for creating summary reports!

DECLARE @lyr VARCHAR(4) = YEAR(DATEADD(yyyy, -1, GETDATE()));

SELECT *
FROM (
        SELECT 
            basecard,
            YEAR(docdate) AS [Year],
            CONVERT(VARCHAR(3), DATEADD(M, MONTH(docdate), -1), 107) AS MONTHS,
            linetotal
        FROM mytbl
        WHERE YEAR(docdate) >= @lyr
    ) AS P
PIVOT
(
    SUM(linetotal)
    FOR MONTHS IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec])
) AS PVT
ORDER BY basecard, [Year];

📊 Example Result: This transforms rows like (101319, 2015-01-01, 132.99) into columns like BaseCard: 101319, Year: 2015, Jan: 132.99, Feb: 188.89...

Conditional Aggregation

Calculate different totals in one query using CASE statements - like having multiple measuring cups!

SELECT 
    CustomerID,
    SUM(CASE WHEN YEAR(OrderDate) = 2015 THEN Total END) AS Total2015,
    SUM(CASE WHEN YEAR(OrderDate) = 2014 THEN Total END) AS Total2014
FROM Orders
GROUP BY CustomerID;

🪟 Window Functions - The Smart Way

Row Numbers

Add sequential numbers to your results - perfect for pagination or ranking!

SELECT *, 
       ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn
FROM (
        SELECT DISTINCT 
            *
        FROM YourTable
     ) AS T
ORDER BY rn;

LEAD and LAG - Compare with Previous/Next

Look at the row before or after the current one. Great for tracking changes over time!

SELECT
    EmployeeID,
    PayDate,
    Salary,
    LAG(Salary, 1) OVER (PARTITION BY EmployeeID ORDER BY PayDate) AS PrevSalary,
    LEAD(Salary, 1) OVER (PARTITION BY EmployeeID ORDER BY PayDate) AS NextSalary
FROM Payroll;

💰 Real World Use: Perfect for tracking salary changes, stock prices, or temperature readings over time!

🧹 Data Cleaning & Maintenance

Find Duplicate Records

Spot those pesky duplicates before they cause problems in your reports.

SELECT ColumnA, COUNT(*) AS Cnt
FROM MyTable
GROUP BY ColumnA
HAVING COUNT(*) > 1;

Remove Duplicates (Keep One)

Clean up duplicates while keeping one good copy - like organizing your recipe box!

WITH dupes AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY (SELECT 0)) AS rn
    FROM MyTable
)
DELETE FROM dupes WHERE rn > 1;

⚠️ Safety First: Always backup your data before running DELETE statements!

Find Gaps in Sequential Numbers

Identify missing numbers in a sequence - useful for invoice numbers, order IDs, etc.

SELECT t1.ID + 1 AS MissingStart
FROM Numbers t1
LEFT JOIN Numbers t2 ON t2.ID = t1.ID + 1
WHERE t2.ID IS NULL;

🎓 Advanced Techniques

CROSS APPLY - Correlated Subqueries Made Easy

Get related data for each row efficiently - like looking up the last order for each customer.

SELECT 
    c.CustomerID,
    x.LastOrderDate
FROM Customers c
CROSS APPLY (
    SELECT MAX(OrderDate) AS LastOrderDate
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
) AS x;

String Aggregation

Combine multiple rows into a comma-separated list - the old school way that still works everywhere!

SELECT 
    Department,
    STUFF((
        SELECT ', ' + EmployeeName
        FROM Employees e2
        WHERE e2.Department = e1.Department
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS EmployeeList
FROM Employees e1
GROUP BY Department;

🔧 Modern Alternative: In SQL Server 2017+, you can use STRING_AGG() function instead!

Update with JOIN

Update records based on data from another table - very powerful for data maintenance!

UPDATE t
SET t.Status = 'Inactive'
FROM Users t
JOIN UserLogin u ON u.UserID = t.UserID
WHERE u.LastLogin < '2015-01-01';

🛡️ Test First: Always test UPDATE statements with SELECT before running them!

⭐ Best Practices & Tips

🔍

Always Test First

Before running UPDATE or DELETE statements, test them as SELECT queries first. It's like tasting your soup before serving!

📚

Use Descriptive Names

Name your columns and aliases clearly. Future you (and your coworkers) will thank you!

⚡

Index Your Joins

Make sure the columns you're joining on have indexes. It's the difference between a quick lookup and searching through every drawer!

🎯

Be Specific

Use specific date ranges and WHERE clauses. Don't pull more data than you need - it's like bringing home the whole grocery store!

← Back to Study & Wisdom