I have a warehouse fact table that has a clustered index on a BIGINT column. Due to circumstances beyond my control, I have to add in data to the fact table from another source where the keys from the two different source systems overlap (I want the data from the second source in its own datamart but I was overruled). To handle this I am adding 1000000 to the key and multiplying it by -1.
If I am inserting all these negative keys into a clustered index, does it add more overhead when reorganizing or rebuilding the index vs continually adding keys > 0 that just get larger and larger.
Thanks
Well, yes, it does. Think of a list written on a couple of paper sheets that is sorted. Adding an entry at the end will be no problem: If there’s enough space on the last page, the entry can be written on that page, and if the last page is full, you can just add a blank page at the end and write the entry on that page. But what if you have to add a new entry at the beginning of the list? All entries on the first page have to be shifted down to make room for the new entry, and if the page is full, you have to add a new page… In SQL Server, this new page will not be in front of the other pages, so the list of pages will get fragmented, which will impact query performance.
If you do not need the records to be physically sorted on disk by that key, consider to change the index to be non-clustered.