SQL Server › Pivoting & Unpivoting Data

What performance considerations should you keep in mind when using PIVOT or UNPIVOT on very large datasets?

Advanced

Answer

Both operations still require SQL Server to process and aggregate the underlying data, so performance depends heavily on whether appropriate indexes exist on the columns used for grouping and filtering, and pivoting a very wide range of distinct values into many separate columns can become inefficient, sometimes making a well designed reporting table or a business intelligence tool a better long term solution for very large scale pivoting needs.

-- Ensure an index supports the underlying aggregation
CREATE INDEX IX_YearlySales_Category_Year
ON YearlySales(Category, SalesYear) INCLUDE (Revenue);

Real-world example A team notices a PIVOT based report slowing down significantly as their sales data grows, and after adding a covering index to support the underlying aggregation, the report's performance improves substantially.

Common follow-ups: At what point does it make sense to move pivoting logic into a dedicated reporting or business intelligence tool instead of doing it in SQL?;How do you measure whether PIVOT is actually the performance bottleneck in a slow report?

Query Optimization & Plans;Indexes