Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
6  
It's not possible for the DROP to start before the CREATE has finished. This seems to be a lot of effort to keep this a heap. Why don't they just create a clustered index and then they can reorganize / rebuild as necessary? Or upgrade to 2008 where they can rebuild a heap much more easily? –  Aaron Bertrand Nov 20 '12 at 1:54
2  
Well you know my opinion on your chosen approach, so we can skip that bit. The DROP will not occur before the CREATE statement completes. Each step of your batch will complete before the next starts. –  Mark Storey-Smith Nov 20 '12 at 1:55
    
More importantly, why wouldn't you just use ALTER TABLE ... REBUILD if they really want it to remain a heap? –  Robert L Davis Nov 20 '12 at 2:07
1  
@Robert - because it's not SQL Server 2008 –  孔夫子 Nov 20 '12 at 2:11
1  
You should also read Paul's post. I understand you're on 2005 but it provides some valuable insight in the follow through link that goes to Kim: sqlskills.com/blogs/paul/post/… –  Ali Razeghi Nov 20 '12 at 2:21

2 Answers 2

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.

share|improve this answer

Part of the reason that you don't get the heap to shrink as much as you expect is that there's 8 bytes in each row for the dummy column. That might be large, depending on the average size of a typical row. Then you drop column dummy, leaving that space unused. Try this without adding and removing dummy and make your clustered index on other columns. They don't have to be unique.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.