SQL Server: the enterprise workhorse

Where SQL Server earned its place

The ERP and van-sales platforms I built for Gulf clients — Masafi, MH Dubai, Xtend Sales — lived and died by transactional integrity: orders, payments, stock movements, credit control. SQL Server’s strength is exactly there: rock-solid ACID behavior, mature tooling, and a query optimizer that handles the gnarly reporting joins an ERP accumulates over years. For .NET shops it remains the lowest-friction serious database.

Patterns from the trenches

• Stored procedures for transactional workflows: order posting, stock check-in/out, end-of-day settlement — complex multi-table transactions live close to the data with explicit transaction scopes.
• Triggers used sparingly and audibly: audit trails yes, business logic no — a trigger that changes behavior invisibly is a debugging tax on every future engineer.
• Indexes designed from query plans, not intuition: the covering index that fixed Masafi’s route-planning report came from the actual plan, not a guess.
• Dapper over heavy ORMs for hot paths: full control of the SQL where milliseconds matter, EF where productivity matters.

Production troubleshooting, systematized

• Start with the wait stats, not the query — SQL Server tells you whether it’s CPU, IO, locking, or memory before you read a single plan.
• Parameter sniffing is the usual suspect for “it was fast yesterday” — know OPTIMIZE FOR and query store forcing before you need them.
• Blocking chains from long transactions: keep transactions short, never hold one across a network call — the rule that prevents half of all incidents.
• Baseline everything: you cannot recognize abnormal without a recorded normal.

Worked example: end-of-day settlement, transactionally

The van-sales pattern — a stored procedure owning a multi-table transaction with explicit scope and honest error handling:

CREATE PROCEDURE dbo.PostEndOfDay @RouteId INT, @PostedBy INT
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;
    BEGIN TRAN;
        UPDATE s SET s.Status = 'Posted', s.PostedAt = SYSUTCDATETIME()
        FROM dbo.Sale s WHERE s.RouteId = @RouteId AND s.Status = 'Confirmed';

        INSERT dbo.StockMovement (ProductId, Qty, Reason, RouteId)
        SELECT si.ProductId, -SUM(si.Qty), 'EOD_SALE', @RouteId
        FROM dbo.SaleItem si
        JOIN dbo.Sale s ON s.Id = si.SaleId AND s.RouteId = @RouteId
        WHERE s.Status = 'Posted' AND s.PostedAt >= CAST(GETDATE() AS DATE)
        GROUP BY si.ProductId;

        INSERT dbo.CashSummary (RouteId, Amount, PostedBy)
        SELECT @RouteId, SUM(TotalAmount), @PostedBy
        FROM dbo.Sale WHERE RouteId = @RouteId AND Status = 'Posted';
    COMMIT;
END

XACT_ABORT ON guarantees no half-posted day survives an error — the whole transaction rolls back or the whole day posts.