February 25, 2020 at 7:41 pm
I was wondering if someone could give me some good examples or explanations of why I would use the Guid type for a column in my table. Thanks !!!
February 25, 2020 at 10:49 pm
One use would be for synchronization purposes - merge replication being an example. This article has an explanation of this and mentions some of the performance issues with this:
Primary Key design considerations for Merge Replication
Sue
February 26, 2020 at 4:07 am
There are a lot of disadvantages to GUIDs as key columns in indexes, especially Clustered Indexes. They basically violate a good number of what most people consider to be desirable attributes for Clustered Index Keys. For example, they not narrow and they're not ever increasing. If you're using Type 4 GUIDs (like what SQL Server uses), they're not even guaranteed to be unique especially between multiple machines.
There are some huge advantages as well. Sue mentioned one of the advantages above. It also makes company mergers a whole lot easier. Since they are Type 4 GUIDs in SQL Server, they are based on a very level pseudo-random sequence that will spread out to all pages of a given index. That means that there are no hot spots during rapid-fire single row inserts. Of course, that also means that an insert with a large number of rows can lock up a table instead of just inserting at the logical end of an index. Since all the pages in the table will take new data, you can run into a problem for large loads because it'll load the whole bloody table into memory to do the inserts on.
Because Type 4 GUIDs are random GUIDs, a lot of people thing they they're a fragmentation problem and nothing could be further from the truth. If you setup the correct FILL FACTOR for your daily load, you can go weeks and even months without any page splits at all, either good or bad. The key is that you have to change your index maintenance a bit. NEVER do a REORGANIZE on a GUID keyed index, clustered or otherwise. REORGANIZE will actually remove critical free space in GUID indexes and that will perpetuate page massive splits every single day! ONLY do REBUILDs on GUID keyed indexes and ONLY if you've assigned a correct FILL FACTOR. Doing index maintenance of any kind on indexes that have a 0, 100, or not-low-enough FILL FACTOR will setup the index for massive page splits. And, no... the extra memory taken up by having a reduced FILL FACTOR isn't wasted. It doesn get filled up and it does prevent all page split. Just don't forget to do a REBUILD when you go over 1% logical fragmentation because EVERY page is getting ready to split then because they all fill up and run out of space at about the same time... but, like I said, it can take weeks or months for that to happen depending on the insert load on the index.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply