4

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

  • 2
    What is the definition of the Contact table? Is it a heap or does it have a clustered index? – Martin Smith Aug 23 '15 at 20:42
  • @Stuart: Neither delete nor truncate command free's space and gives it back to OS after the operation. You manually need to run dbcc shrinkfile command to claim space or if AUTO SHRINK is enabled it would free up space automatically when shrink operation kicks in. – Shanky Aug 24 '15 at 6:16
8

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.

| improve this answer | |
  • 1
    Also, if a an entire extent becomes empty, the freed extent can be reused by other objects. I hate poorly worded exam material but it prepares one for the real world where functional requirements are unclear, vague, or just plain not thought through. – Dan Guzman Aug 23 '15 at 21:40
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.

| improve this answer | |
  • 1
    In SQL Server it would be SELECT * INTO (no CTAS). Also, truncate may not be possible if the table has inbound foreign key references. – Aaron Bertrand Aug 23 '15 at 21:19
  • Thanks for the heads-up. I may be using SQL Server soon, so I may even start asking questions! – Vérace Aug 23 '15 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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