Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there are now gaps in the primary key column.

Is there any impact to the performance of the clustered index on these tables as a result of these "missing" identities?

From my understanding as long as the index b-tree is refreshed then there shouldn't be any problems, but I can't find anything specific to confirm this.

Thanks

share|improve this question

migrated from stackoverflow.com Jan 27 '12 at 13:21

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

up vote 8 down vote accepted

I don't see how gaps in your identity values could have A N Y negative impact on your performance.

After all - the values are still all 4-byte integer, they're still as optimal for JOINs as anything can be ..... and even if you have gaps, the IDENTITY values are ever-increasing....

Of course, if you physically deleted rows from your clustered index, then there will be "holes" in your pages (the "Swiss Cheese" problem :-) ) - but those would be fixed by a nightly database maintenance that you certainly do have in place, don't you??

And even if you do occasionally delete a row from your table, the impact on your index fragmentation will be marginal.

share|improve this answer
3  
+1. Spot on...... –  Mitch Wheat Jan 27 '12 at 6:35
3  
Sure, but is there any performance penalty from having gaps between the letters of the word any? :p –  Andrew Barber Jan 27 '12 at 6:39
    
@AndrewBarber: not entirely sure on that one :-) –  marc_s Jan 27 '12 at 7:01
1  
    
Thanks @marc_s. I had assumed that was the case and couldn't think of any reason why it wouldn't be. And yes we do have nightly maintenance plans to fix the fragmentation –  Greg Jan 30 '12 at 2:26
add comment

Addition and deletion of data over time will fragment your clustered index. These are the gaps you are seeing and they will decrease performance. This fragmentation is fixed by doing an index reorganization or an index rebuild. This is the maintenance @marc_s is talking about.

General guidelines are to use index reorganization for lightly fragmented indexes and index rebuilds for medium to highly fragmented indexes.

I hope this is what you meant by 'index b-tree is refreshed '.

share|improve this answer
    
Thanks @StanleyJohns. That is what I meant by "index b-tree is refreshed". Couldn't remember the word I was looking for –  Greg Jan 30 '12 at 23:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.