DELETE on a heap table

Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my production environment.

Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space.

Otherwise, you're looking at:

• ALTER TABLE (mytablename) REBUILD - which takes it offline

• Doing deletes with TABLOCK hints

• TRUNCATE TABLE (mytablename)

I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have to deal with deletes (which cause the empty space problem) and updates (which cause the forwarded fetches problem.)

StackExchange Database Administrators Q#169436, answer score: 11