-- 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;
:
By a Senior Database Architect Approximate reading time: 25 minutes Introduction: Beyond CRUD For decades, the role of the SQL Server Developer has been misunderstood. Many see it as simply writing SELECT , INSERT , UPDATE , and DELETE statements. But in the modern data landscape, the SQL Server Developer is an architect of logic, a guardian of performance, and a bridge between transactional systems and business intelligence. 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: -- Store JSON, query it, and update it
-- Running total per customer, ordered by date SELECT OrderId, CustomerId, OrderDate, Amount, SUM(Amount) OVER (PARTITION BY CustomerId ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal, LAG(Amount, 1, 0) OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS PreviousOrderAmount FROM Orders; Simplify date grouping. : Enable RCSI on your OLTP databases (unless
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.
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.