What is the performance cost of using PIVOT in Microsoft SQL Server

I concur with Bruce. PIVOT as such does not incur a particular performance overhead.

Take a query like this one:

SELECT P.ProductName, Cy.CountryName, SUM(OD.Amount) AS Amount
FROM   [Order Details] OD 
JOIN   Products P   ON P.ProductID    = OD.ProductID
JOIN   Orders O     ON OD.OrderID     = O.OrderID
JOIN   Customers C  ON C.CustomerID   = O.CustomerID
JOIN   Countries Cy ON Cy.CountryCode = C.CountryCode
GROUP  BY P.ProductName, Cy.CountryName
ORDER  BY P.ProductName, Cy.CountryName;

This query has to scan large tables from start to end (since there is no WHERE clause), so it is certainly expensive.

If you now decide to pivot this result so that you get one column per country, that is a bit of rearrangement, but the overhead is limited. The big cost is scanning the tables.

It is someone different if you build a dynamic pivot, since this requires an initial step where you scan the data to find all countries. And if the column you are to pivot by is not indexed, this is one more full scan of the table. However, there are some mitigating factors:

1. You only have to scan one table, and in this example that may be the Customers table which is a lot smaller than Orders and Order Details.
2. The column you are pivoting by is often indexed, making the scan cheaper, as only the narrow index has to be scanned.

the pivot does not add a high performance. its just an sum of grouped rows. the cost is the cost of the grouping and any filtering by the where clause. with the correct index, its a single seek with sequential read. without a proper index can be a full database scan, sort, and then read.