Which command Deletes and Frees Space

Question

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).

Answer 1

Microsoft exams (and preparation material) tends to be stupid in some cases. It works best with excluding answers. You have to keep records? So truncate is not an option for you (and creating a new table is typically not the right way to solve it).

Delete does free up the space. Of course not the harddisk space but (again typically) the space inside the clustered index. The background that the ghost cleanup process has to really tidy up is too complex for 98-364. This exam is about database fundamentals.

Answer 2

Not sure of the exact syntax for SQL Server (there's an edit that says that your RDBMS is SQL Server), but I'd do something like this (I'm making the assumption that a TRANSACTION is a - single - command :-). I don't think it's possible in a single line command.

(pseudocode)

BEGIN TRANSACTION;
CREATE TABLE dummy AS SELECT * FROM My_Original_Table WHERE country = 'USA';
TRUNCATE  My_Original_Table;
INSERT INTO My_Original_Table SELECT * FROM dummy;
DROP TABLE dummy;
COMMIT;

This might be of interest.