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