For me, it’s CTE’s. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.

What construct do you like using on a daily basis?

  • itty53@vlemmy.net
    link
    fedilink
    arrow-up
    3
    ·
    edit-2
    1 year ago

    You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.

    Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.

    Example

    CREATE VIEW AS const.OrderType
    SELECT 
    CAST (1 as 'New'),
    CAST (2 as 'Pending'),
    CAST (3 as 'Shipped')
    GO
    
    -- Assuming a table with OrderId and OrderTypeId
    
    SELECT         o.OrderId 
    FROM           dbo.Orders AS o
    CROSS JOIN const.OrderType AS ot
    WHERE         o.OrderTypeId = ot.[Pending]
    
    -- Only returns orders where TypeId = 2