3

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

Improve this question
6
  • 7
    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, 2012 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, 2012 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, 2012 at 2:07
  • 1
    @Robert - because it's not SQL Server 2008
    – 孔夫子
    Nov 20, 2012 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 - AWS
    Nov 20, 2012 at 2:21

2 Answers 2

Reset to default
6

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.

Improve this answer
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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