RE: how many clustered indexes can u create for a tabl...
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.
RE: how many clustered indexes can u create for a table in DWH?In case of truncate and delete commanda what happens to table which has unique id.
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!
