opbang

 Profile Answers by opbang 

• Sep 22nd, 2006

 

You can have only one clustered index per table.

If you use delete command, you can rollback... it fills your redo log files.

If you do not want records, you may use truncate command... which will be faster... and doesn't fill your redo log files.

Hakoonamatata

 Profile Answers by Hakoonamatata 

• Jul 9th, 2007

 

By definition, a clustered index  physically arranges all data in a table in a sequential manner. Since you can not have more than one physical arrangements of data in a table, you can have just one clustered index per table.

In case of truncate, all data is deleted without any transaction log entry, hence the unique ID can have the same values again, while in case of delete, the unique ID will have newer values in the new data load.

ecoverdale

 Profile Answers by ecoverdale 

• Dec 4th, 2007

 

In SQL Server terms:

Only one clustered index is permitted on a table.

When a delete statement is performed it is a logged transaction which can be rolled back.  The delete does nothing to the identity column of the table. 

A truncate is also logged and can be rolledback.  The truncate will reset the identity column. 

The big difference between delete and truncate is that the delete statement records individual row deletes in the transaction log while the truncate records data page deallocations.  The rollback ability of a truncate is pretty easy to verify with a simple test.  Begin a transaction, truncate a table that you know has records, and rollback the transaction.  This test is best done with test tables!