SQL Server GUIDs

[Thad Bogert]

Hi Vik,

Thanks for the info.  We are developing a data conversion utility for a database developed by a third party; so we don’t have control over the schema.

I will probably just cast the GUIDs into character columns and be done with it. Although it would be a nice enhancement for the ODBC DAM to automatically do the conversion. :)


Thanks Again,

Thad

> On Aug 14, 2020, at 8:07 PM, omnislist at keys2solutions.com.au wrote:
> 
> Hi Thad,
> 
> GUIDs… pfffffttttt! If you have a say, please get rid of them. OR just run this SQL drop table <insert_your_guid_tablename_here>;  ;-) 
> 
> Okay… So now that’s out of the system… 
> 
> But... GUIDs are bad! Like really bad, here are a *few* reasons why… (stolen from another article, link at the end…)
> 1. GUIDs have a huge domain but the chances of GUID collisions are always there, very slim, but there.
> 2. It is recommended that if you are relying on GUID’s uniqueness in your table/database, then you are in BIG trouble and need to think again! Uniqueness of the row should be enforced using a Unique or Primary Key constraint on the table.
> 3. GUIDs are stored as LOB Data! Trust me when I am saying this, its a PITA (this is me saying!)
> 4. Its non-sequential, ergo, statistics on it are screwed! Indexes well… might as well just whip out a journal/excel and start over! Did I mention that they are stored as LOBs, yeah they are heavy buggers! 
> 5. CRUD operations are S L O W! 
> 6. Lookups are S L O W E R
> 7. Index Fragmentation is High
> 8. Joins suffer, a.k.a their performance is tragically poor.
> 9. These trigger Forward scans
> 10. If stored on VLDBs on a HADR AOAG Cluster, replication is painful.
> [Reference: https://docs.microsoft.com/en-us/archive/blogs/sqlserverfaq/guid-vs-int-debate] 
> 
> I can go on and on… I think you can see a pattern here, since I am not a GUID ;-) 
> 
> I don’t think I bear any animosity towards them… :-p 
> 
> So… There is a (epically long) story behind that, we can talk on the side if you like. But let me circle back to your original question.
> 
>> But I am hoping that someone knows of a more elegant solution to working with GUIDs?
> 
> There are a few ways to work with these critters.
> 1. Computed columns: Have a non-persisted (or a persisted) (yes, that is what it/they is/are called) computed column on those tables that return the cast’ed version of the GUIDs. Like a mini view.
> 2. Use a view.
> 3. Have you considered having a SequentialGUID - BIGINT table and all other tables use values from this table for global uniqueness, but its still BIGINT. Then use these BIGINTs as unique row identifiers. The cost on your joins would still be less. (Link is in the reference article.)
> 4. Remember that GUIDs are 36 characters in length so use them wisely, limit your char fields to that length only. At least you’ll not waste any more LOB space. :-p 
> 5. CAST () or CONVERT (), these both will suffer cardinality issues in your query performance. 
> 6. Dare I say use checksum() on GUIDs, yes there is a **greater** likelihood of collision here, but its still faster, readable and workable.
> 
> Finally, GUIDs and elegance, yeah, just about as elegant as a rampaging baboons in a delicate dinnerware shop. (Could’t help myself with that parting jab!)  
> 
> Regards,
> 
> Vik Shah
> Keys2Solutions 
> m: +61 411 493 495
> 
>> On 15 Aug 2020, at 06:44, Thad Bogert <thad at technosolver.com> wrote:
>> 
>> Hi All,
>> 
>> I am doing some work with a SQL Server database where all the primary keys are GUIDs.  When records containing GUIDs are fetched into Omnis the GUID columns return as binary (which they are).  The challenge with binary datatypes that they are not directly visible when viewing them in a list; and they must be converted to character if you wish to use the GUID in a subsequent query.
>> 
>> I’m aware that it is possible to cast() or convert() the columns to a character type. But I am hoping that someone knows of a more elegant solution to working with GUIDs?
>> 
>> 
>> Thanks,
>> 
>> Thad
>> 
>> 
>> 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com