Do we need to rebuild index after truncate?
No, you don’t generally need to reindex after TRUNCATE – and if you do, you’re much better off dropping the indexes, loading data, then re-creating the indexes at the end.
Is there any difference between disabling clustered and non clustered index?
When a clustered index is disabled, its data rows cannot be accessed. This means that there will be no insertion process possible. On the other hand, when non-clustered indexes are disabled, all the data related to it are physically deleted, but the definition of the index is kept in the system.
Why do we need to rebuild indexes?
When and how often should you Rebuild Indexes? The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics.
Does truncate remove Index?
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
Can we alter index in SQL Server?
To modify an index Right-click the index that you want to modify and then click Properties. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.
What happens when you drop clustered index in ETL?
If you drop the clustered index, the footprint of the table will double because the HEAP will be created before the CI is dropped. Then, when you do recreate the CI, same thing will happen. And BOTH evolutions take a pretty good bit of time. Tell us more about the ETL process that does this load.
When do you need an index in a data warehouse?
In the early stages of data warehouses some tables will not need an index, but as time goes by and more data is added to the table you may find the need to add one or more indexes to the table to ensure optimal performance.
What happens when you disable an index in SQL Server?
By disabling an index, you have basically turned off the index, but have allowed SQL Server to retain the index definition in the database metadata. If you want the index back all you need to do is rebuild the index.
Why are you dropping and Recreating Your indexes?
It is common practice to drop indexes and then create them to optimize the data load process. By not having the indexes at load time SQL Server can load the data much faster, because there are no indexes to update. This is a common practice for optimizing loading large data warehouse table loads.