-- 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;
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
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. -- Split: Get all orders for given list
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. CREATE FUNCTION dbo
-- 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) |
:
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.