SQL Server pivot vs. multiple join

The answer will of course be “it depends” but based on testing this end…

Assuming

1. 1 million products
2. product has a clustered index on product_id
3. Most (if not all) products have corresponding information in the product_code table
4. Ideal indexes present on product_code for both queries.

The PIVOT version ideally needs an index product_code(product_id, type) INCLUDE (code) whereas the JOIN version ideally needs an index product_code(type,product_id) INCLUDE (code)

If these are in place giving the plans below

then the JOIN version is more efficient.

In the case that type 1 and type 2 are the only types in the table then the PIVOT version slightly has the edge in terms of number of reads as it doesn’t have to seek into product_code twice but that is more than outweighed by the additional overhead of the stream aggregate operator

PIVOT

Table 'product_code'. Scan count 1, logical reads 10467
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 3297 ms,  elapsed time = 3260 ms.

JOIN

Table 'product_code'. Scan count 2, logical reads 10471
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 1906 ms,  elapsed time = 1866 ms.

If there are additional type records other than 1 and 2 the JOIN version will increase its advantage as it just does merge joins on the relevant sections of the type,product_id index whereas the PIVOT plan uses product_id, type and so would have to scan over the additional type rows that are intermingled with the 1 and 2 rows.