-- Old mess: DATEADD(month, DATEDIFF(month, 0, OrderDate), 0) -- New clean: SELECT DATETRUNC(month, OrderDate) AS OrderMonth, SUM(Amount) FROM Orders GROUP BY DATETRUNC(month, OrderDate); -- Time-series bucketing (every 3 days) SELECT DATE_BUCKET(day, 3, OrderDate) AS Bucket, COUNT(*) FROM Orders GROUP BY DATE_BUCKET(day, 3, OrderDate); Nothing ruins a production system faster than deadlocks and long-held locks. 4.1 Isolation Levels – Choose Wisely | Level | Anomaly Prevented | Concurrency Impact | |-------|-------------------|--------------------| | READ UNCOMMITTED (or NOLOCK hint) | None (dirty reads possible) | High (no shared locks) | | READ COMMITTED (default) | Dirty reads | Medium (locks held briefly) | | REPEATABLE READ | Non-repeatable reads | Lower (holds locks until end of transaction) | | SERIALIZABLE | Phantom reads | Very low (range locks) | | READ COMMITTED SNAPSHOT (RCSI) | Dirty reads via row versioning | High (no locks for readers) |
: Enable RCSI on your OLTP databases (unless legacy code depends on locking). It eliminates most deadlocks. 4.2 Deadlock Analysis Pattern When a deadlock occurs, capture it using: sqlserver developer
Happy coding. May your seeks be narrow and your scans be none. About the author: 15+ years of SQL Server experience across financial, retail, and logistics sectors. Microsoft Data Platform MVP. Microsoft Data Platform MVP
UPDATE Orders SET OrderDetails = JSON_MODIFY(OrderDetails, '$.shipping.tracking', 'UPS123') WHERE OrderId = 500; If you still use subqueries for running totals or row numbers, stop. Your flight recorder. Force good plans
EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_outer_command = 1; It shows: blocking chains, query text, plan, tempdb usage, and more. Your flight recorder. Force good plans, revert bad ones.
-- Store JSON, query it, and update it without parsing entire document DECLARE @json NVARCHAR(MAX) = N'"customer": "name":"John", "orders":["id":1]'; SELECT JSON_VALUE(@json, '$.customer.name') AS CustomerName;
CREATE FUNCTION dbo.fn_SecurityPredicate(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS is_valid WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'; CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_SecurityPredicate(SalesRep) ON dbo.Sales; Hide data from non-privileged users without changing app code.