Sqlserver | Developer

-- Trace flag 1222 logs deadlocks to ERRORLOG DBCC TRACEON(1222, -1); Then analyze the graph. The most common fix: ensure all procedures access tables in the .

:

-- 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) | sqlserver developer

: 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: -- Trace flag 1222 logs deadlocks to ERRORLOG

-- 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; capture it using: -- Store JSON

-- Split: Get all orders for given list of IDs DECLARE @Ids VARCHAR(MAX) = '101,205,389,476'; SELECT o.* FROM Orders o INNER JOIN STRING_SPLIT(@Ids, ',') AS s ON o.OrderId = CAST(s.value AS INT); -- Aggregate: Build a comma-separated list of product names for a category SELECT CategoryId, STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName) AS Products FROM Products GROUP BY CategoryId;