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?
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 enum.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 enum.OrderType AS ot WHERE o.OrderTypeId = ot.[Pending] -- Only returns orders where TypeId = 2, no need to know what Id that is or for anyone else to in the future either.Hey, that’s pretty neat. Perhaps you could wrap a the OrderType view in a CTE and join on that to get the values back?