Sql-server – Defrag a HEAP by creating clustered index and immediately dropping it

clustered-indexindexscriptingsql-server-2005t-sql

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any permanent changes – they did okay this, though.)

ALTER TABLE my_heap ADD dummy BIGINT IDENTITY;
GO
CREATE CLUSTERED INDEX ix_dummy ON my_heap (dummy);
GO
-- need to wait here until index completes
DROP INDEX ix_dummy ON my_heap;
ALTER TABLE my_heap DROP COLUMN dummy;
GO

When I run individual steps of the script, it works fine. When I run it all at once, nothing seems to happen (frag levels don't go down). I believe it's because the DROP happens before the CREATE CLUSTERED has finished – so the CREATE CLUSTERED doesn't have time to do its stuff.

How do I make the DROP wait for the CREATE CLUSTERED to finish? I'm doing this for about 30 tables, and so I'd rather not put in a simple WAITFOR DELAY 15 minutes for each one.

PS Using SQL Server 2005

Best Answer

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.

Related Question