SQL WORLD: T-SQL Best Practices: Writing Enterprise-Grade Clean Code for Intermediate Developers

As someone who's spent years architecting SQL Server solutions and reviewing code in enterprise environments, I can tell you that writing clean and maintainable T-SQL code separates intermediate developers from senior architects. While beginners focus on making queries work, intermediate developers must master code quality, performance optimization, and architectural patterns that scale across large teams and complex databases.

In this advanced guide, I'll share the T-SQL best practices I've learned through production incidents, performance tuning sessions, and code reviews at enterprise scale. We'll explore sophisticated techniques using the WideWorldImporters sample database, covering everything from error handling patterns to query optimization strategies that will elevate your SQL development skills.

Let me walk you through the professional-grade practices that will make your T-SQL code production-ready and maintainable.

Advanced Naming Conventions and Code Organization

Schema-Qualified Object References

Always use fully-qualified object names (schema.object) in production code. This eliminates ambiguity and improves query plan caching.

Problematic Approach:

SELECT CustomerName, PhoneNumber
FROM Customers  -- Which schema? Sales? dbo?
WHERE CustomerID = 1001;

Professional Standard:

SELECT 
    cust.CustomerName,
    cust.PhoneNumber,
    cust.DeliveryAddressLine1,
    city.CityName,
    sp.StateProvinceName
FROM Sales.Customers AS cust
    INNER JOIN Application.Cities AS city 
        ON cust.DeliveryCityID = city.CityID
    INNER JOIN Application.StateProvinces AS sp 
        ON city.StateProvinceID = sp.StateProvinceID
WHERE cust.CustomerID = 1001;

Standardized Variable Naming with Hungarian Notation

Use prefixes to make data types immediately obvious in complex stored procedures.

CREATE PROCEDURE Sales.usp_GetCustomerOrderSummary
    @piCustomerID INT,
    @pdStartDate DATE,
    @pdEndDate DATE,
    @piMinOrderValue DECIMAL(18, 2) = 0,
    @pbIncludeBackorders BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @dtProcessingDate DATETIME2 = SYSDATETIME();
    DECLARE @nvCustomerName NVARCHAR(100);
    DECLARE @iOrderCount INT;
    
    -- Procedure logic here
END;

Prefix Convention:

@pi = Parameter, Integer
@pd = Parameter, Date
@pb = Parameter, Bit/Boolean
@nv = Variable, NVARCHAR
@dt = Variable, DATETIME/DATETIME2
@i = Variable, Integer
@dc = Variable, Decimal

Robust Error Handling and Transaction Management

Implementing TRY-CATCH with Proper Transaction Control

Professional T-SQL code must handle errors gracefully and maintain data integrity through proper transaction management.

CREATE PROCEDURE Purchasing.usp_CreatePurchaseOrder
    @piSupplierID INT,
    @piContactPersonID INT,
    @piDeliveryMethodID INT,
    @tvOrderLines Purchasing.PurchaseOrderLineType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Critical for proper error handling
    
    DECLARE @iPurchaseOrderID INT;
    DECLARE @dtOrderDate DATE = CAST(SYSDATETIME() AS DATE);
    DECLARE @nvErrorMessage NVARCHAR(4000);
    DECLARE @iErrorSeverity INT;
    DECLARE @iErrorState INT;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate supplier exists and is active
        IF NOT EXISTS (
            SELECT 1 
            FROM Purchasing.Suppliers 
            WHERE SupplierID = @piSupplierID
        )
        BEGIN
            THROW 50001, 'Invalid SupplierID provided', 1;
        END;
        
        -- Insert purchase order header
        INSERT INTO Purchasing.PurchaseOrders (
            SupplierID,
            OrderDate,
            DeliveryMethodID,
            ContactPersonID,
            ExpectedDeliveryDate,
            LastEditedBy
        )
        VALUES (
            @piSupplierID,
            @dtOrderDate,
            @piDeliveryMethodID,
            @piContactPersonID,
            DATEADD(DAY, 14, @dtOrderDate),
            1  -- System user
        );
        
        SET @iPurchaseOrderID = SCOPE_IDENTITY();
        
        -- Insert order lines from table-valued parameter
        INSERT INTO Purchasing.PurchaseOrderLines (
            PurchaseOrderID,
            StockItemID,
            OrderedOuters,
            ExpectedUnitPricePerOuter,
            LastEditedBy
        )
        SELECT 
            @iPurchaseOrderID,
            StockItemID,
            OrderedOuters,
            ExpectedUnitPricePerOuter,
            1
        FROM @tvOrderLines;
        
        -- Verify at least one line was inserted
        IF @@ROWCOUNT = 0
        BEGIN
            THROW 50002, 'Purchase order must contain at least one line item', 1;
        END;
        
        COMMIT TRANSACTION;
        
        -- Return the new PurchaseOrderID
        SELECT @iPurchaseOrderID AS PurchaseOrderID;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
        
        -- Capture error details
        SELECT 
            @nvErrorMessage = ERROR_MESSAGE(),
            @iErrorSeverity = ERROR_SEVERITY(),
            @iErrorState = ERROR_STATE();
        
        -- Log error to audit table (assuming one exists)
        -- INSERT INTO dbo.ErrorLog (...)
        
        -- Re-throw the error to the calling application
        RAISERROR(@nvErrorMessage, @iErrorSeverity, @iErrorState);
        
        RETURN -1;
    END CATCH;
END;
GO

Key Error Handling Principles:

Always use SET XACT_ABORT ON in procedures with transactions
Capture error details before re-throwing
Roll back transactions in CATCH blocks
Log errors for troubleshooting
Return meaningful error codes
Use THROW for modern error handling (SQL Server 2012+)

Performance-Optimized Query Patterns

Using Window Functions for Complex Analytics

Window functions eliminate self-joins and provide cleaner, more efficient code for analytical queries.

Calculate Running Totals and Rankings:

WITH CustomerOrderAnalytics AS (
    SELECT 
        cust.CustomerID,
        cust.CustomerName,
        ord.OrderID,
        ord.OrderDate,
        SUM(line.Quantity * line.UnitPrice) AS OrderTotal,
        -- Running total of sales per customer
        SUM(SUM(line.Quantity * line.UnitPrice)) 
            OVER (
                PARTITION BY cust.CustomerID 
                ORDER BY ord.OrderDate 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS RunningTotal,
        -- Rank orders by value within each customer
        ROW_NUMBER() 
            OVER (
                PARTITION BY cust.CustomerID 
                ORDER BY SUM(line.Quantity * line.UnitPrice) DESC
            ) AS OrderValueRank,
        -- Calculate days since last order
        DATEDIFF(
            DAY, 
            LAG(ord.OrderDate) OVER (
                PARTITION BY cust.CustomerID 
                ORDER BY ord.OrderDate
            ),
            ord.OrderDate
        ) AS DaysSinceLastOrder
    FROM Sales.Customers AS cust
        INNER JOIN Sales.Orders AS ord 
            ON cust.CustomerID = ord.CustomerID
        INNER JOIN Sales.OrderLines AS line 
            ON ord.OrderID = line.OrderID
    WHERE ord.OrderDate >= '2016-01-01'
    GROUP BY 
        cust.CustomerID,
        cust.CustomerName,
        ord.OrderID,
        ord.OrderDate
)
SELECT 
    CustomerID,
    CustomerName,
    OrderID,
    OrderDate,
    OrderTotal,
    RunningTotal,
    OrderValueRank,
    ISNULL(DaysSinceLastOrder, 0) AS DaysSinceLastOrder
FROM CustomerOrderAnalytics
WHERE OrderValueRank <= 5  -- Top 5 orders per customer
ORDER BY CustomerID, OrderDate;

Optimizing with Proper Indexing Hints and Query Patterns

-- Complex query with explicit index hints for performance-critical scenarios
SELECT 
    item.StockItemID,
    item.StockItemName,
    item.UnitPrice,
    hold.QuantityOnHand,
    supplier.SupplierName,
    -- Calculate months of inventory on hand
    CASE 
        WHEN item.TypicalWeightPerUnit > 0 THEN
            CAST(
                hold.QuantityOnHand / 
                NULLIF(
                    (
                        SELECT AVG(line.Quantity)
                        FROM Sales.OrderLines AS line WITH (NOLOCK)  -- Read uncommitted for reporting
                        WHERE line.StockItemID = item.StockItemID
                            AND line.OrderID IN (
                                SELECT OrderID 
                                FROM Sales.Orders 
                                WHERE OrderDate >= DATEADD(MONTH, -3, GETDATE())
                            )
                    ), 
                    0
                ) * 3  -- 3 months average
                AS DECIMAL(10, 2)
            )
        ELSE NULL
    END AS MonthsOfInventory
FROM Warehouse.StockItems AS item
    INNER JOIN Warehouse.StockItemHoldings AS hold 
        ON item.StockItemID = hold.StockItemID
    LEFT JOIN Purchasing.Suppliers AS supplier 
        ON item.SupplierID = supplier.SupplierID
WHERE item.IsChillerStock = 0
    AND hold.QuantityOnHand > 0
ORDER BY MonthsOfInventory ASC;

Advanced Code Modularity Techniques

Creating Reusable Table-Valued Functions

Table-valued functions promote code reuse and encapsulation of complex logic.

CREATE FUNCTION Sales.fn_GetCustomerOrderMetrics
(
    @pdStartDate DATE,
    @pdEndDate DATE,
    @piMinimumOrders INT = 1
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        cust.CustomerID,
        cust.CustomerName,
        cust.CustomerCategoryID,
        COUNT(DISTINCT ord.OrderID) AS TotalOrders,
        SUM(line.Quantity * line.UnitPrice) AS TotalRevenue,
        AVG(line.Quantity * line.UnitPrice) AS AvgLineValue,
        MIN(ord.OrderDate) AS FirstOrderDate,
        MAX(ord.OrderDate) AS LastOrderDate,
        DATEDIFF(DAY, MIN(ord.OrderDate), MAX(ord.OrderDate)) AS CustomerLifespanDays,
        COUNT(DISTINCT line.StockItemID) AS UniqueProductsPurchased
    FROM Sales.Customers AS cust
        INNER JOIN Sales.Orders AS ord 
            ON cust.CustomerID = ord.CustomerID
        INNER JOIN Sales.OrderLines AS line 
            ON ord.OrderID = line.OrderID
    WHERE ord.OrderDate BETWEEN @pdStartDate AND @pdEndDate
    GROUP BY 
        cust.CustomerID,
        cust.CustomerName,
        cust.CustomerCategoryID
    HAVING COUNT(DISTINCT ord.OrderID) >= @piMinimumOrders
);
GO

-- Using the function in queries
SELECT 
    metrics.CustomerName,
    metrics.TotalOrders,
    metrics.TotalRevenue,
    metrics.AvgLineValue,
    metrics.UniqueProductsPurchased,
    cat.CustomerCategoryName
FROM Sales.fn_GetCustomerOrderMetrics('2016-01-01', '2016-12-31', 5) AS metrics
    INNER JOIN Sales.CustomerCategories AS cat 
        ON metrics.CustomerCategoryID = cat.CustomerCategoryID
WHERE metrics.TotalRevenue > 50000
ORDER BY metrics.TotalRevenue DESC;

Implementing Dynamic SQL Safely

When dynamic SQL is necessary, use sp_executesql with parameters to prevent SQL injection.

CREATE PROCEDURE Sales.usp_GetOrdersByDynamicCriteria
    @pnvOrderByColumn NVARCHAR(50),
    @pnvSortDirection NVARCHAR(4) = 'ASC',
    @pdStartDate DATE,
    @pdEndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Whitelist allowed column names to prevent SQL injection
    IF @pnvOrderByColumn NOT IN ('CustomerName', 'OrderDate', 'OrderTotal', 'OrderID')
    BEGIN
        RAISERROR('Invalid sort column specified', 16, 1);
        RETURN;
    END;
    
    IF @pnvSortDirection NOT IN ('ASC', 'DESC')
    BEGIN
        RAISERROR('Invalid sort direction specified', 16, 1);
        RETURN;
    END;
    
    DECLARE @nvSQL NVARCHAR(MAX);
    DECLARE @nvParams NVARCHAR(500);
    
    SET @nvSQL = N'
    SELECT 
        ord.OrderID,
        cust.CustomerName,
        ord.OrderDate,
        SUM(line.Quantity * line.UnitPrice) AS OrderTotal,
        COUNT(line.OrderLineID) AS LineItemCount
    FROM Sales.Orders AS ord
        INNER JOIN Sales.Customers AS cust 
            ON ord.CustomerID = cust.CustomerID
        INNER JOIN Sales.OrderLines AS line 
            ON ord.OrderID = line.OrderID
    WHERE ord.OrderDate BETWEEN @pdStartDateParam AND @pdEndDateParam
    GROUP BY 
        ord.OrderID,
        cust.CustomerName,
        ord.OrderDate
    ORDER BY ' + QUOTENAME(@pnvOrderByColumn) + ' ' + @pnvSortDirection + ';';
    
    SET @nvParams = N'@pdStartDateParam DATE, @pdEndDateParam DATE';
    
    EXEC sp_executesql 
        @nvSQL,
        @nvParams,
        @pdStartDateParam = @pdStartDate,
        @pdEndDateParam = @pdEndDate;
END;
GO

Code Documentation and Maintenance Strategies

Comprehensive Header Comments

Every stored procedure and function should have standardized header documentation.

/*******************************************************************************
* Procedure Name: Sales.usp_GenerateMonthlyRevenueReport
* 
* Purpose: Generates aggregated revenue metrics by customer category and 
*          sales territory for monthly executive reporting
*
* Author: [Your Name]
* Created: 2024-12-14
* Modified: 2024-12-14
*
* Parameters:
*   @piReportYear INT - The year for the report (Required)
*   @piReportMonth INT - The month for the report (1-12) (Required)
*   @pbIncludeBackorders BIT - Include backordered items in calculations (Default: 0)
*   @pnvOutputFormat NVARCHAR(20) - 'SUMMARY' or 'DETAILED' (Default: 'SUMMARY')
*
* Returns: Result set with revenue metrics
*
* Error Handling: Uses TRY-CATCH with transaction rollback
*
* Performance: Optimized with CTE and proper indexing
*   - Expected execution time: < 2 seconds for 1 year of data
*   - Uses indexes: IX_Orders_OrderDate, IX_OrderLines_OrderID
*
* Dependencies:
*   - Sales.Orders
*   - Sales.OrderLines
*   - Sales.Customers
*   - Sales.CustomerCategories
*
* Example Usage:
*   EXEC Sales.usp_GenerateMonthlyRevenueReport 
*       @piReportYear = 2016,
*       @piReportMonth = 5,
*       @pbIncludeBackorders = 0,
*       @pnvOutputFormat = 'SUMMARY';
*
* Change History:
*   Date        Author          Description
*   ----------  --------------  ------------------------------------------------
*   2024-12-14  Developer Name  Initial creation
*
*******************************************************************************/
CREATE PROCEDURE Sales.usp_GenerateMonthlyRevenueReport
    @piReportYear INT,
    @piReportMonth INT,
    @pbIncludeBackorders BIT = 0,
    @pnvOutputFormat NVARCHAR(20) = 'SUMMARY'
AS
BEGIN
    -- Procedure implementation
END;
GO

Common Intermediate-Level Mistakes to Avoid

Mistake 1: Ignoring Query Execution Plans

Always analyze execution plans for queries that will run frequently or against large datasets.

-- Enable actual execution plan before running
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Complex query with potential performance issues
SELECT 
    cust.CustomerName,
    COUNT(DISTINCT ord.OrderID) AS OrderCount,
    SUM(line.Quantity * line.UnitPrice) AS TotalRevenue
FROM Sales.Customers AS cust
    LEFT JOIN Sales.Orders AS ord 
        ON cust.CustomerID = ord.CustomerID
    LEFT JOIN Sales.OrderLines AS line 
        ON ord.OrderID = line.OrderID
WHERE ord.OrderDate >= '2016-01-01'
    OR ord.OrderDate IS NULL  -- This OR condition can hurt performance
GROUP BY cust.CustomerName
OPTION (RECOMPILE);  -- Force recompilation to see current statistics

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Review the execution plan for:

Index scans vs. index seeks
Missing index recommendations
High-cost operations (sorts, hash matches)
Implicit conversions
Parallelism issues

Mistake 2: Not Using Parameter Sniffing Awareness

Parameter sniffing can cause performance issues when the same procedure performs differently based on parameters.

-- Problem: First execution parameters affect all subsequent plans
CREATE PROCEDURE Sales.usp_GetOrdersByDateRange
    @pdStartDate DATE,
    @pdEndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Without OPTION(RECOMPILE) or OPTION(OPTIMIZE FOR), 
    -- this query may have inconsistent performance
    SELECT 
        ord.OrderID,
        ord.OrderDate,
        cust.CustomerName,
        SUM(line.Quantity * line.UnitPrice) AS OrderTotal
    FROM Sales.Orders AS ord
        INNER JOIN Sales.Customers AS cust 
            ON ord.CustomerID = cust.CustomerID
        INNER JOIN Sales.OrderLines AS line 
            ON ord.OrderID = line.OrderID
    WHERE ord.OrderDate BETWEEN @pdStartDate AND @pdEndDate
    GROUP BY 
        ord.OrderID,
        ord.OrderDate,
        cust.CustomerName
    OPTION (RECOMPILE);  -- Force new plan each execution for varying data ranges
END;
GO

Mistake 3: Improper Temp Table vs Table Variable Usage

Understanding when to use each temporary storage option is critical for performance.

CREATE PROCEDURE Warehouse.usp_AnalyzeInventoryTurnover
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Use temp table for large result sets (better statistics)
    CREATE TABLE #StockMovement (
        StockItemID INT NOT NULL,
        StockItemName NVARCHAR(100),
        TotalSold INT,
        AvgQuantityPerOrder DECIMAL(10, 2),
        LastSaleDate DATE,
        PRIMARY KEY CLUSTERED (StockItemID)
    );
    
    INSERT INTO #StockMovement
    SELECT 
        item.StockItemID,
        item.StockItemName,
        SUM(line.Quantity) AS TotalSold,
        AVG(line.Quantity) AS AvgQuantityPerOrder,
        MAX(ord.OrderDate) AS LastSaleDate
    FROM Warehouse.StockItems AS item
        INNER JOIN Sales.OrderLines AS line 
            ON item.StockItemID = line.StockItemID
        INNER JOIN Sales.Orders AS ord 
            ON line.OrderID = ord.OrderID
    WHERE ord.OrderDate >= DATEADD(YEAR, -1, GETDATE())
    GROUP BY 
        item.StockItemID,
        item.StockItemName;
    
    -- Now use the temp table in subsequent queries
    SELECT 
        sm.StockItemName,
        sm.TotalSold,
        sm.AvgQuantityPerOrder,
        sm.LastSaleDate,
        hold.QuantityOnHand,
        CASE 
            WHEN sm.AvgQuantityPerOrder > 0 THEN 
                CAST(hold.QuantityOnHand / sm.AvgQuantityPerOrder AS DECIMAL(10, 1))
            ELSE NULL
        END AS MonthsOfInventory
    FROM #StockMovement AS sm
        INNER JOIN Warehouse.StockItemHoldings AS hold 
            ON sm.StockItemID = hold.StockItemID
    ORDER BY MonthsOfInventory DESC;
    
    -- Cleanup
    DROP TABLE #StockMovement;
END;
GO

Guidelines:

Use temp tables (#TempTable) for:
- Large result sets (> 100 rows)
- When you need statistics and indexes
- Multiple operations on the same data
Use table variables (@TableVar) for:
- Small result sets (< 100 rows)
- Simple operations
- When transaction logging overhead matters

Advanced Testing and Validation Patterns

Unit Testing Your T-SQL Code

Implement validation checks within your procedures for data quality assurance.

CREATE PROCEDURE Sales.usp_ValidateAndProcessOrder
    @piOrderID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @iErrorCount INT = 0;
    DECLARE @nvErrorMessages NVARCHAR(MAX) = '';
    
    -- Validation 1: Order exists
    IF NOT EXISTS (
        SELECT 1 
        FROM Sales.Orders 
        WHERE OrderID = @piOrderID
    )
    BEGIN
        SET @iErrorCount += 1;
        SET @nvErrorMessages += 'Order does not exist. ';
    END;
    
    -- Validation 2: Order has line items
    IF NOT EXISTS (
        SELECT 1 
        FROM Sales.OrderLines 
        WHERE OrderID = @piOrderID
    )
    BEGIN
        SET @iErrorCount += 1;
        SET @nvErrorMessages += 'Order has no line items. ';
    END;
    
    -- Validation 3: All items are in stock
    IF EXISTS (
        SELECT 1
        FROM Sales.OrderLines AS line
            INNER JOIN Warehouse.StockItemHoldings AS hold 
                ON line.StockItemID = hold.StockItemID
        WHERE line.OrderID = @piOrderID
            AND hold.QuantityOnHand < line.Quantity
    )
    BEGIN
        SET @iErrorCount += 1;
        SET @nvErrorMessages += 'Insufficient stock for one or more items. ';
    END;
    
    -- If validation fails, return errors
    IF @iErrorCount > 0
    BEGIN
        SELECT 
            @piOrderID AS OrderID,
            'VALIDATION_FAILED' AS Status,
            @iErrorCount AS ErrorCount,
            @nvErrorMessages AS ErrorMessages;
        RETURN;
    END;
    
    -- Process order (actual business logic here)
    SELECT 
        @piOrderID AS OrderID,
        'SUCCESS' AS Status,
        0 AS ErrorCount,
        'Order validated and processed successfully.' AS Message;
END;
GO

Summary: Enterprise-Grade T-SQL Development

Mastering clean, maintainable T-SQL code at the intermediate level requires discipline, architectural thinking, and attention to production realities. Let's recap the essential practices:

✅ Always use schema-qualified names and consistent naming conventions
✅ Implement comprehensive error handling with TRY-CATCH and transaction control
✅ Leverage window functions for efficient analytical queries
✅ Create reusable functions and modular code components
✅ Use dynamic SQL safely with sp_executesql and parameter validation
✅ Document thoroughly with standardized header comments
✅ Understand execution plans and optimize query performance
✅ Be aware of parameter sniffing and plan caching issues
✅ Choose appropriate temp storage based on data volume and operations
✅ Validate data quality within your procedures
✅ Test rigorously before deploying to production

Remember: clean code isn't just about aesthetics—it's about creating reliable, performant, maintainable database solutions that scale with your organization. The patterns and practices demonstrated with WideWorldImporters provide a foundation you can apply immediately to real-world projects.

As you continue developing your skills, focus on understanding why these practices matter in production environments, not just how to implement them. Performance, maintainability, and reliability are the hallmarks of professional SQL development.

Frequently Asked Questions

Q: When should I use stored procedures versus inline SQL in my application?
A: Use stored procedures for complex business logic, operations requiring multiple statements, or frequently-executed queries that benefit from plan caching. Use inline SQL for simple, dynamic queries where the flexibility of building SQL in application code outweighs the benefits of encapsulation. In enterprise environments, stored procedures provide better security through permission management and easier performance tuning.

Q: How do I handle deadlocks in production T-SQL code?
A: Implement retry logic in your application layer for deadlock victims (error 1205). In T-SQL, minimize deadlock potential by accessing tables in the same order across procedures, keeping transactions short, using appropriate isolation levels (consider READ COMMITTED SNAPSHOT), and ensuring proper indexing. Use Extended Events or SQL Profiler to identify deadlock patterns, then analyze the deadlock graph to understand which resources are causing conflicts.

Q: What's the best way to manage database migrations and schema changes in version-controlled environments?
A: Use a migration-based approach with tools like SQL Server Data Tools (SSDT), Flyway, or custom migration scripts. Maintain scripts in version control with timestamps or version numbers, include both UP (apply) and DOWN (rollback) scripts, test migrations in development and staging environments first, and always backup production before deploying. For complex changes, use techniques like expand-contract pattern: add new columns/tables first, migrate data, then remove old structures.

T-SQL Best Practices: Writing Enterprise-Grade Clean Code for Intermediate Developers