I'm reading through the 98-364 exam
book and there's a question I can't quite answer:
"What command would you use to remove all records from the Contact table that do not have a country of USA and at the same time free the space used by those records?"
If "that do not have a country of USA" wasn't in there, it would simply be "The Truncate Command" as it would wipe out all data in that table, and free the space. To my knowledge the delete command doesn't free the space, and I can't find a command that both deletes and frees the space while target specific records.
Does the book have a typo here? Or is there a command anyone knows of?
Note: It's "What command...", not commands.
Edit: (Stuart) There's been a lot of useful answers here, and it seems to me that the book is somewhat ambiguous. Outside of the 'Which Command would you use", and in the event this comes up on the exam, what would the "expected" answer be? I would assume they are looking for "The delete command with where clause". Simply because they only suggest three methods in the book up to this point (Delete/Truncate/Drop).
dbcc shrinkfile
command to claim space or ifAUTO SHRINK
is enabled it would free up space automatically when shrink operation kicks in. – Shanky Aug 24 '15 at 6:16