TheSQLGuru (3/19/2013)
Eugene Elutin (3/19/2013)
2) why aren't you using NEWSEQUENTIALID??
I don't know why OP doesn't, but me...
I don't use it because it's useless! It does not guarantee sequential order after reboot as it's just a simple wrapper around UuidCreateSequential WinAPI which never had order guarantee...
BTW, I try not to use GUID's at all, however sometimes it's unavoidable. The company I'm consulting right now had bought into 3-rd party product which has CLUSTERED GUID's PKS in all tables.
So, I enjoy looking into index fragmentation stats every day here :w00t:...
You are absolutely wrong in your statement. It is certainly NOT useless. It avoids fragmentation for as long as the server remains up. You currently (with NEWID) get values spread throughout the range continually. That isn't just about page splits, fragmentation, less full pages, etc either. It keeps hot (i.e. recent) data in the buffer pool much more frequently as well.
I note that if you DO have GUIDs you MUST manage them, or suffer a variety of negative consequences. Some of those consequences you can't do anything about however (such as size of data) other than buying bigger hardware. 🙁
Ok, the "not guaranteed sequence" is not a single NEWSEQUENTIALID issue.
Actually it's doesn't guarantees even uniqueness in all cases (if your computer has no NIC installed, then GUID generetated by that function will only be unique within this computer). But, when you have NIC installed, it makes it unsecured, as it's based on the MAC address of NIC and can be predicted.
All about beauties of it is here: http://msdn.microsoft.com/en-gb/library/windows/desktop/aa379322(v=vs.85).aspx
Another issue you will face with this if you have .NET application which also will want to generate "sequential" GUID's, you will need to come up with a special logic to match SQL Server "byte scrambling" (http://www.jorriss.net/blog/archive/2008/04/24/unraveling-the-mysteries-of-newsequentialid).
So, I do hold my humble opinion that the best way to manage GUID's in SQL Server database is to try avoiding them completely :hehe:
And the last bit about
It avoids fragmentation for as long as the server remains up.
That is really helpful! So, as long you never reboot your server - you are fine. But if you do it at least once in its life, than you staffed, as sequential GUID's could be lower in its values than generated before reboot, so you will get your fragmentation issue back.
So, make sure you never reboot your server in order to enjoy full set of NEWSEQUENTIALID powerful features;-)
Sorry, it's not my cup of tea...