Sample Header Ad - 728x90

SQL Server 2014: Why is Clustered Index Rebuild MUCH faster than initial index creation?

-1 votes
1 answer
397 views
I have a table where rows consist of a few GUID ids and then a huge byte array of data. There is a partitioned clustered index on this table, which orders data by the GUIDs. The table is large, containing ~3tb of data, most of which is the byte array of data, which is not a part of the index. When first creating the index, it took well over a day, but the index rebuild (not reorganize) took under an hour. **Why did the rebuild go so much quicker?** My understanding is it would have to drop and re-create the clustered index, which would involve it re-writing this large table all over again. It's might be worth noting that when lookin at size reports for the table, the table itself takes ~3tb but the index is 1gb or less. Also worth noting, the table wasn't partitioned until that initial index create I did previously, while the rebuild was done on an already partitioned table. Not sure if that makes an impact. Again, just trying to gain understanding. Why would my rebuild be so significantly faster than the initial create? EDIT: It is the **clustered index** I initially created and was re-building. There are no other indexes on this table
Asked by frobot (19 rep)
Sep 30, 2022, 03:30 PM
Last activity: Oct 6, 2022, 06:33 PM