Sql-server – Postgres heap vs SQL Server clustered index

Fragmented heaps matter. The larger the heap, the harder it is to walk the data. Performance suffers.

Creating and dropping the CI will reorder the rows in the table as you are aware. If the vendor offers no solution and it is your responsibility to address the fragmentation, that's exactly what I have done in the past. I wouldn't leave the CI if it were me, I'd drop it. I would avoid adding a column since that does alter the function of the table permanently, and the same argument could be made if you leave the CI in place.

If you do this be sure that you have appropriate storage for creating the CI since it will require storage at least equal to the size of the heap, and also consider when you do it re: your service level requirements.

How do I make the DROP wait for the CREATE CLUSTERED to finish?

You don't, it already does. The statements are sequential, i.e. synchronous, one after the other.

What is heap storage? Paul Randal explains

So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom right corner, collates the pages in sequential order, binds them, and then... unbinds and throws them back onto the desk again in a heap. The total achievement is near zero. Sure you will regain some usage from tidying up and removing forwarded pages, the data pages may be a tad bit more compact. Other than that, it's just as messy as you started.