Guids

Transact-SQL
1

I was wondering when I should use a GUID in a table.

2

hi

all depends on what you want to do !!

it may be necessary to use GUID
or
may not be necessary to use GUID

3

What do you mean it depends on what I want to do. I asked when should I use a GUID. So do you know of any instances for when what I want to do would need a GUID? I understand if you are not that familiar with using GUIDs.

4

I try to use guids as much as possible. Devs hate it, but it makes porting data from one db to another alot easier. If we use Identity columns, then we can get identity collisions, where ID 1 in db 1 is also in DB2 but belongs to a separate entity. I.E. Purchase Order ID 1 might be to Home Depot in 1 db, but ID 1 might be to Lowes in db 2.

2 Likes
5

As a bit of a sidebar, if you end up using GUIDs, a lot of people will tell you how bad the related indexes will fragment. That's because the current (supposed) "Best Practices" actually perpetuate page splits and the resulting fragmentation. The bottom line is that you DO need to set a reasonable Fill Factor for RANDOM GUID based indexes and REBUILD them when they reach 1% logical fragmentation. NEVER use REORGANIZE on RANDOM GUID indexes because THAT's what perpetuates the fragmentation. REORGANIZE simply does not work the way most people think. To summarize, it is NOT capable of creating new pages like REBUILD can and so it tries to fill pages up to the Fill Factor but cannot create extra pages to "unfill" pages back down to the Fill Factor. What that does is it actually removes the extra space the Random GUIDs need to grow without massive page splits and makes the page splits much worse.

If you build the index on the Random GUIDs with a decent Fill Factor according to the work load (I use 71, 81, or 91 where the "1" remindes me that it's a Random GUID and needs to be REBUILT after it reashes 1% fragmentation), such an index can go literally for months with absolutely NO page splits and the memory isn't actually wasted... it get's filled as the pages fill.

It's totally worth doing this way. To make a much longer story shorter, I have about 400 hours of experimentation on the subject and that's how I know about this.

3 Likes
6

thanks Jeff

very insight full ..

7

You're welcome and thank you for the feedback.