-- 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;
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. sqlserver developer
-- 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. -- Split: Get all orders for given list
– SQL Server now rivals NoSQL for document storage. Happy coding
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.
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.