When creating a CONNECT PIVOT table, the engine executes SELECT * on the target table. This seems really inefficient to me.

In an ideal situation, there will be some usable index on the pivot column, so SELECT DISTINCT pivotCol will be much faster.

To find the names of columns, a separate query to find table structure could be executed. I can't think of why the engine would need to do SELECT * when creating the table.

Is there some explanation I'm missing?