Question 21

Explanation

Correct option:

For large tables TRUNCATE is faster than DELETE.

• TRUNCATE is a DDL (Data Definition Language) operation, meaning it removes all rows without logging individual row deletions, making it significantly faster.

• DELETE, on the other hand, is a DML (Data Manipulation Language) operation that logs each row deletion, which can slow down performance for large tables.

Incorrect options:

• "You can TRUNCATE a table if foreign key constraints will be violated." → False

  • You cannot truncate a table that is referenced by a foreign key unless the foreign key constraint is first removed or disabled.

• "For tables with multiple indexes and triggers DELETE is faster than TRUNCATE." → False

  • DELETE processes row-by-row, meaning triggers and indexes are affected for each row deleted, making it slower than TRUNCATE.

• "You can never DELETE rows from a table if foreign key constraints will be violated." → False

• You can delete rows, but only if the foreign key constraint allows it (for example, using ON DELETE CASCADE or ON DELETE SET NULL in the foreign key definition).

The option begins with "You can never" which makes it incorrect. Sometimes things may be possible even though they may not be proper or a good practice, or may cause undesirable consequences.

In the test it is important to recognize between "should" or "can". Should refers to something that follows a proper practice like "one can do something" but they "should not".

DELETE and TRUNCATE

DELETE and TRUNCATE are the commands use to remove tuples from a relation, but they differ in many contexts. In SQL, DELETE command is a Data Manipulation Language command whereas, TRUNCATE command is a Data Definition Language command.

However, the point that allows us to differentiate between DELETE and TRUNCATE is that DELETE is able to remove specified tuples from a relation, Whereas, the TRUNCATE command removes entire tuples from a relation.

TRUNCATE is faster than DELETE

DELETE is a logged operation on a per row. This means that the deletion of each row is recorded & removed physically. You can delete any row that does not involve a violation of restriction, while leaving the foreign key or any other restrictions in place.

TRUNCATE is a logged operation, but in a different way. TRUNCATE logs the deal location of information pages in which information exist. The de allocation of information pages means that your information rows still actually exist in the information pages, but the extensions are marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE. Can not truncate a table that has foreign key constraints. You must remove the constraints, truncate the table, & reapply the constraints.

TRUNCATE will reset any identity columns to default value initialization. This means that if you have a table with an identity column & has 264 rows with a seed value of 1, his latest record will have the value 264 (assuming you started with value 1 in the columns of your identity. After truncating your table when you insert a new record in to the empty table, the identity column will have a value of 1 DELETE won't. In the same scenario, if the current row, inserting a new row in to the empty table, the identity column has a value of 265.

Why Truncate is faster than Delete

In writing to delete all information is copied to the rollback table space & then delete operation is performed. So when you type ROLLBACK after deleting a table, you can get back the information (the method get it for the pace of Rollback Tables). This whole technique will take time, but when using TRUNCATE, it removes information directly without copying it to Rollback table space. TRUNCATE is faster.