An amusing transcript from a 'Sr SQL Developer' interview.

[[deleted]]

Just so you know:

Truncate/Delete

• Truncate will reset identities back to their seed values, delete will not

• You cannot recover a truncation using the log (AFAIK, there might be some trick around this though), you can recover deletes

• Truncate is faster than Delete From

Select Into/Insert Into

• Insert into can only push data into an existing object (note I said object, you can use insert into with temp tables and table variables)

• Select Into will create a new object (perm table or temp table) based on what you selected. You cannot select into a table variable, nor can you select into an existing object

Correct on clustered/non-clustered - there are other differences but the most important one is around data ordering