3

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
7
  • 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
    Commented Nov 20, 2013 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
    Commented Nov 20, 2013 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
    Commented Nov 20, 2013 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
    Commented Nov 20, 2013 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
    Commented Nov 20, 2013 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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