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.

MSDN learns me that NEWSEQUENTIALID():

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

So a newly generated GUID can be lower than a previously generated GUID. Isn't this highly undesirable in terms of index fragmentation and such? And if so: is there some way to provide NEWSEQUENTIALID with a seed of some sort, to prevent this?

Or isn't this as bad as it seems as seeing the servers won't restart that often en we can deploy maintenance scripts for rebuilding indexes?

share|improve this question

1 Answer 1

No way to seed it. That whole "globally unique" part gets in the way.

the bigger question here is "do you truly require this?"

GUIDs are big, GUIDs -even with newsequentialid, can lead to index fragmentation. If you need GUIDa where do you need them? They typically do not make great clustered index candidates. For both of these reasons and more.

share|improve this answer
    
I'm not really looking to get into the holy war of GUIDs v.s. INTs as keys, but suffice to say this is a business thing, we need global uniqueness. When I joined the team IDs were generated as NEWID() and as such this is already a good improvement, but the pseudo-randomness that stems from resetting sequences bothers me. And perhaps seeding wasn't the best choice of words, maybe something like pick-up-were-you-stopped-last-time would be more appropriate :) –  Apeiron Nov 20 '13 at 12:19
    
So primary keys do not HAVE to be the clustered key. Something to keep in mind. But no. I knew what you meant. No way to seed, start where left off, etc. If you are restarting so much that this becomes a concern by itself you have bigger worries ;-) –  Mike Walsh Nov 20 '13 at 12:21
    
I think you'll properly need a business application to issues keys if you want to do better than the sequential function's ..... "issues" with a server restart. And yes, the Primary Key does not need to be the clustered index, which is very worth keeping in mind :) –  Allan S. Hansen Nov 20 '13 at 12:25
1  
So perhaps something like using an int as CI and a rowid for global unique rows could be an idea? –  Apeiron Nov 20 '13 at 12:30
1  
What columns are you often sorting or range-searching on? Are they reasonably small? If so, use them as the clustered index columns. Remember that the clustered index does not have to be the primary key (as already mention) but that it also does not have to be unique. In other words, it doesn't have to be a candidate key. –  Greenstone Walker Nov 20 '13 at 21:19

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.